SQL Server Information

Only a short script to get informations about your SQL Server enviroment, like machine name, instance name, edition, version, level, cluster security, user mode, collation and login. I use it as a piece on my whole admin scripts.

--> SQL Server Information <--
SET nocount ON;goUSE [master];goSELECT
    CONVERT(CHAR(100), Serverproperty('MachineName')) AS 'MACHINE NAME',
    CONVERT(CHAR(50), Serverproperty('ServerName')) AS 'SQL SERVER NAME',

        (CASE WHEN CONVERT(CHAR(50), Serverproperty('InstanceName')) IS NULL
                THEN 'Default Instance'
              ELSE CONVERT(CHAR(50), Serverproperty('InstanceName'))
         END) AS 'INSTANCE NAME',

        CONVERT(CHAR(30), Serverproperty('EDITION')) AS EDITION,
        CONVERT(CHAR(30), Serverproperty('ProductVersion')) AS 'PRODUCT VERSION',
        CONVERT(CHAR(30), Serverproperty('ProductLevel')) AS 'PRODUCT LEVEL',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 1
                THEN 'Clustered'
              WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 0
                THEN 'NOT Clustered'
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'FAILOVER CLUSTERED',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 1
                THEN 'Integrated Security '
              WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 0
                THEN 'SQL Server Security '
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'SECURITY',

        (CASE WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 1
                THEN 'Single User'
              WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 0
                THEN 'Multi User'
              ELSE 'INVALID INPUT/ERROR'
         END) AS 'USER MODE',

        CONVERT(CHAR(30), Serverproperty('COLLATION')) AS COLLATION,
    Getdate() AS RunTime,
    @@SPID AS 'ID',
    SYSTEM_USER AS 'Login Name?',
    USER AS 'User Name';go
-- END SQL Server Information --

sql_server_informations.sql

Qui tacet, consentire videtur

sql server, sysadmin, version

SQL Server

Print article

This entry was posted by Torsten Schuessler on 2013-02-20 at 14:13:00 . Follow any responses to this post through RSS 2.0.

Related Posts
Trackbacks (0)
Comments (4)

Comment from: Frank Kalis
2013-02-20 @ 16:44:30

I would consider making it available as a file to download. :-)
Comment from: Torsten Schuessler
2013-02-20 @ 16:52:29

ups, my fault
...
it's done!
THX Frank, wish you a nice day!
Comment from: Henrik
2013-03-21 @ 10:58:03

Hello Thorsten,

great script and nice idea to use Serverproperty instead @@version.

I merge my script and yours to a more completer one:

--> SQL Server Information <--
SET nocount ON;
go
USE [master];
go
SELECT
CONVERT(CHAR(100), Serverproperty('MachineName')) AS 'MACHINE NAME',
CONVERT(CHAR(50), Serverproperty('ServerName')) AS 'SQL SERVER NAME',

(CASE WHEN CONVERT(CHAR(50), Serverproperty('InstanceName')) IS NULL
THEN 'Default Instance'
ELSE CONVERT(CHAR(50), Serverproperty('InstanceName'))
END) AS 'INSTANCE NAME',
@@LANGUAGE as [Language],
right(substring(@@version,charindex(' - ',@@version)+17,PATINDEX('%Copyright%',@@version)-27-(charindex(' - ',@@version)+17)),3) as [32,64],

CONVERT(CHAR(30), Serverproperty('EDITION')) AS EDITION,
substring(@@version,22,(charindex(' - ',@@version)-22)) as [Version],
CONVERT(CHAR(30), Serverproperty('ProductVersion')) AS 'BUILD',
CONVERT(CHAR(30), Serverproperty('ProductLevel')) AS 'PRODUCT LEVEL',

(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 1
THEN 'Clustered'
WHEN CONVERT(CHAR(30), Serverproperty('ISClustered')) = 0
THEN 'NOT Clustered'
ELSE 'INVALID INPUT/ERROR'
END) AS 'FAILOVER CLUSTERED',

(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 1
THEN 'Integrated Security '
WHEN CONVERT(CHAR(30), Serverproperty('ISIntegratedSecurityOnly')) = 0
THEN 'SQL Server Security '
ELSE 'INVALID INPUT/ERROR'
END) AS 'SECURITY',

(CASE WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 1
THEN 'Single User'
WHEN CONVERT(CHAR(30), Serverproperty('ISSingleUser')) = 0
THEN 'Multi User'
ELSE 'INVALID INPUT/ERROR'
END) AS 'USER MODE',

CONVERT(CHAR(30), Serverproperty('COLLATION')) AS COLLATION,
Getdate() AS RunTime,

substring(@@version,charindex (' ON ', @@VERSION)+4,(charindex (' (Build', @@VERSION))- (charindex (' ON ', @@VERSION)-1)-5) as [OS Version],
substring(@@version,charindex ('Build ', @@VERSION) + 6,4) as [OS Build],
case when charindex ('Service Pack', @@VERSION) = 0
then ''
else
substring(@@version,charindex ('Service Pack', @@VERSION) +13,1 )
end as [OS Service Pack],

@@SPID AS 'ID',
SYSTEM_USER AS 'Login Name?',
USER AS 'User Name';
go
-- END SQL Server Information --
Comment from: Torsten Schuessler
2013-03-21 @ 11:21:46

Hello Henrik,

nice merging! THX!
If it is for you okay, I'd like to customize the script?

cu
tosc