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

  • *****
    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!

  • ****-
    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

In order to leave a comment Log in now!