SQL Server Information
By tosc
4 comments
I would consider making it available as a file to download. :-)
2013-02-20 @ 16:44
Comment from: Henrik
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 --
2013-03-21 @ 10:58