Geeks With Blogs
SharePoint & SQL Thoughts

If you are a DBA you should be familiar with @@Version to get the information for you SQL Server, but whatif you want to get a little more that the @@Version information.

Here is a head start.

CREATE PROCEDURE usp_getserverinfo
AS
IF @@VERSION LIKE ('% 10.%')
BEGIN
SELECT
       SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActualServerName
      ,SERVERPROPERTY('MachineName') as SQLServerName
      ,SERVERPROPERTY('InstanceName') as instancename
      ,(CASE
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 7 THEN 'SQL Server 7'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 8 THEN 'SQL Server 2000'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 9 THEN 'SQL Server 2005'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 10 THEN 'SQL Server 2008'
      ELSE NULL END) SQL_License
      ,SERVERPROPERTY('edition') as Edition
      ,SERVERPROPERTY('ProductVersion') as ProdVersion
      ,SERVERPROPERTY('ProductLevel') as ServicePack
      ,SERVERPROPERTY('IsClustered') as [IsClustered]
      ,SERVERPROPERTY('LicenseType') as licencetype
      ,SERVERPROPERTY('NumLicenses') as NumLicences
      ,SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceUpdate
      ,SERVERPROPERTY('FilestreamShareName') as Filestreamshare
END
ELSE
BEGIN
SELECT
       SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActualServerName
      ,SERVERPROPERTY('MachineName') as SQLServerName
      ,SERVERPROPERTY('InstanceName') as instancename
      ,(CASE
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 7.0 THEN 'SQL Server 7'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 8.0 THEN 'SQL Server 2000'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 9.0 THEN 'SQL Server 2005'
            WHEN CONVERT(VARCHAR(2),SERVERPROPERTY('ProductVersion'))= 10 THEN 'SQL Server 2008'
      ELSE NULL END) SQL_License
      ,SERVERPROPERTY('edition') as Edition
      ,SERVERPROPERTY('ProductVersion') as ProdVersion
      ,SERVERPROPERTY('ProductLevel') as ServicePack
      ,SERVERPROPERTY('IsClustered') as [IsClustered]
      ,SERVERPROPERTY('LicenseType') as licencetype
      ,SERVERPROPERTY('NumLicenses') as NumLicences
      ,SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceUpdate
      ,SERVERPROPERTY('FilestreamShareName') as Filestreamshare
END
Posted on Monday, July 19, 2010 7:26 AM | Back to top


Comments on this post: Get information for your SQL Server

Comments are closed.
Comments have been closed on this topic.
Copyright © Leonard Mwangi | Powered by: GeeksWithBlogs.net