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

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

    I would consider making it available as a file to download. :-)

  • Comment from: tosc
    2013-02-20 @ 16:52:29

    ups, my fault

    it’s done!
    THX Frank, wish you a nice day!

  • 4 stars
    Henrik
    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: tosc
    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

Leave a comment

You must be logged in to leave a comment. Log in now!

If you have no account yet, you can register now...
(It only takes a few seconds!)