Categories
Scripts

Server Property

This will list out the server properties of your sql server including the account under which your services run. This is compatible to SQL 2000 and 2005.

Script

DECLARE @collation SQL_VARIANT
DECLARE @Edition SQL_VARIANT
DECLARE @InstanceName SQL_VARIANT
DECLARE @authmode SQL_VARIANT
DECLARE @lictype SQL_VARIANT
DECLARE @numlics SQL_VARIANT
DECLARE @pversion SQL_VARIANT
DECLARE @plevel SQL_VARIANT
DECLARE @Servername SQL_VARIANT
DECLARE @sqlser VARCHAR(20)
DECLARE @agentser VARCHAR(20)
DECLARE @browser VARCHAR(20)
SELECT @collation=SERVERPROPERTY('Collation')
SELECT @Edition=SERVERPROPERTY('Edition')
SELECT @InstanceName=ISNULL(SERVERPROPERTY('InstanceName'),'Default')
SELECT @authmode=CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Mode' ELSE 'Mixed Mode' END
SELECT @lictype=SERVERPROPERTY('LicenseType')
SELECT @numlics=ISNULL(SERVERPROPERTY('NumLicenses'),'Not Defnied')
SELECT @pversion=SERVERPROPERTY('ProductVersion')
SELECT @plevel=SERVERPROPERTY('ProductLevel')
SELECT @Servername=SERVERPROPERTY('ServerName')
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER\',
@value_name='objectname', @value=@sqlser OUTPUT
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\',
@value_name='objectname', @value=@agentser OUTPUT
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SYSTEM\CurrentControlSet\Services\SQLBrowser\', @value_name='objectname', @value=@browser OUTPUT
PRINT 'Name of Server :'+CONVERT(VARCHAR(30),@Servername)
PRINT 'Name of Server Collation :'+CONVERT(VARCHAR(30),@collation)
PRINT 'Name of Server Editiom :'+CONVERT(VARCHAR(30),@Edition)
PRINT 'Name of Server Instance :'+CONVERT(VARCHAR(30),@InstanceName)
PRINT 'Authentication Mode :'+CONVERT(VARCHAR(30),@authmode)
PRINT 'Type of License :'+CONVERT(VARCHAR(30),@lictype)
PRINT 'Number of Licenses :'+CONVERT(VARCHAR(30),@numlics)
PRINT 'Product Version :'+CONVERT(VARCHAR(30),@pversion)
PRINT 'Product Level :'+CONVERT(VARCHAR(30),@plevel)
PRINT 'Account Starting SQL Server Service:'+CONVERT(VARCHAR(30),@sqlser)
PRINT 'Account Starting SQL Server Agent Service:'+CONVERT(VARCHAR(30),@agentser)
PRINT 'Account Starting SQL Server Browser Service:'+CONVERT(VARCHAR(30),@browser)

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*