script cmd for detecting sqlserver services and read key edition

bibi92
bibi92 used Ask the Experts™
on
Hello,

I search a script for detecting sqlserver services and read key edition.

Thanks

Regards

bibi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

sc query <SERVICE_KEY_NAME> will do the trick.

for example: sc query MSSQLSERVER

It's not clear what do you mean with read key edition.


You can get service key name reading the properties from services.msc console.

HTH
Database Analyst
Commented:
Here's what I run in SQL to produce an detailed report including SQL server info:

SET ANSI_WARNINGS OFF
SET NOCOUNT ON

DECLARE @CmdShellInUse BIT
SET @CmdShellInUse =0x1
IF (CHARINDEX('9.00',CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')))>0)
BEGIN

      SELECT @CmdShellInUse = CONVERT(BIT, Value_In_Use) from sys.configurations WHERE Name = 'xp_cmdshell'
      IF (@CmdShellInUse =0x0)
      BEGIN
            EXEC sp_configure 'xp_cmdshell', 1
            RECONFIGURE WITH OVERRIDE
      END
END


IF (OBJECT_ID('tempdb..#SysInfo') IS NOT NULL)
DROP TABLE #SysInfo
CREATE TABLE #SysInfo (ROWID INT IDENTITY(1,1)  NOT NULL, Property Varchar(255) NULL, Value Varchar(1000) NULL)
--///////////////////////////////////////////////////////////////////////////////////////
-- System Information
--///////////////////////////////////////////////////////////////////////////////////////
INSERT #SysInfo (Property)
EXEC Master.dbo.xp_CMDShell 'systeminfo'
UPDATE #SysInfo SET Property = RTRIM(LEFT(Property, CHARINDEX(':',Property))), Value = LTRIM(RIGHT(Property, LEN(Property)-CHARINDEX(':',Property)))
DELETE #SysInfo WHERE Property IS NULL
OR Property ='OS Manufacturer:'
OR Property ='OS Build Type:'
OR Property = 'Registered Owner:'
OR Property ='Registered Organization:'
OR Property ='Product ID:'
OR Property ='Original Install Date:'
OR Property ='System Directory:'
OR Property ='Logon Server:'
OR Property ='Domain:'
OR Property ='                                 Connection Name:'
OR Property ='                                 DHCP Enabled:'
OR Value ='IP address(es)'
OR VALUE LIKE '%.%.%.%'
OR Value ='File 1'
UPDATE #SysInfo SET Property = SPACE(0), Value = RTRIM(LTRIM(Property)) +Value
WHERE RIGHT(RTRIM(Property),1)=':' AND LEFT(VALUE,1)='\' AND LEN(RTRIM(LTRIM(Property))) =2

--///////////////////////////////////////////////////////////////////////////////////////
-- Windows Services Information
--///////////////////////////////////////////////////////////////////////////////////////

DECLARE @ServiceList varchar(8000)
IF (OBJECT_ID('tempdb..#SCQuery') IS NOT NULL)
DROP TABLE #SCQuery
CREATE TABLE #SCQuery (ROWID INT IDENTITY(1,1) NOT NULL, Service Varchar(255) NULL)
INSERT #SCQuery (Service)
EXEC Master.dbo.xp_CMDShell 'SC QUERY state= all'
DELETE #SCQuery WHERE Service IS NULL
DELETE #SCQuery WHERE Service Like 'DISPLAY_NAME:%' OR  
Service Like '        TYPE               :%' OR
Service Like '        WIN32_EXIT_CODE    :%' OR
Service Like '        SERVICE_EXIT_CODE  :%' OR
Service Like '        CHECKPOINT         :%' OR
Service Like '        WAIT_HINT          :%' OR
Service Like '                                (%'
UPDATE #SCQuery SET Service = REPLACE(Service,'SERVICE_NAME: ','')
UPDATE #SCQuery SET Service = '('+REPLACE(RTRIM(Service),'        STATE              : ','')+'), ' WHERE Service LIKE '        STATE              : %'
UPDATE #SCQuery SET Service = REPLACE(REPLACE(Service, '(1  ','('),'(4  ','(')
UPDATE #SCQuery SET Service = REPLACE(Service,'msftesql','SQLFullTextSearch')
UPDATE sc1 SET Service = sc1.Service + sc2.Service
FROM #SCQuery sc1 INNER JOIN #SCQuery sc2 ON sc2.RowID  = sc1.RowID +3
DELETE #SCQuery WHERE Service Like '(%'
--SELECT * FROM #SCQuery
INSERT #SysInfo (Property, Value) Values ('All Services Status:', SPACE(0))
SET @ServiceList = NULL
SELECT @ServiceList = COALESCE(@ServiceList + '', '') + Service FROM #SCQuery WHERE LEFT(Service,1)<='L' ORDER BY Service

INSERT #SysInfo (Property, Value)
SELECT '                           [A-L]:', LEFT(@ServiceList, LEN(@ServiceList)-1)


SET @ServiceList = NULL
SELECT @ServiceList = COALESCE(@ServiceList + '', '') + Service FROM #SCQuery WHERE LEFT(Service,1)>'L' AND  LEFT(Service,1)<= 'R'ORDER BY Service
INSERT #SysInfo (Property, Value)
SELECT '                           [M-R]:', LEFT(@ServiceList, LEN(@ServiceList)-1)

SET @ServiceList = NULL
SELECT @ServiceList = COALESCE(@ServiceList + '', '') + Service FROM #SCQuery WHERE LEFT(Service,1)>'R' AND  LEFT(Service,1)<= 'T'ORDER BY Service
INSERT #SysInfo (Property, Value)
SELECT '                           [S-T]:', LEFT(@ServiceList, LEN(@ServiceList)-1)

SET @ServiceList = NULL
SELECT @ServiceList = COALESCE(@ServiceList + '', '') + Service FROM #SCQuery WHERE LEFT(Service,1)>'T' ORDER BY Service
INSERT #SysInfo (Property, Value)
SELECT '                           [U-Z]:', LEFT(@ServiceList, LEN(@ServiceList)-1)
--///////////////////////////////////////////////////////////////////////////////////////
-- SQL Services Information
--///////////////////////////////////////////////////////////////////////////////////////
IF (OBJECT_ID('tempdb..#InstalledSQLInstances') IS NOT NULL) DROP TABLE #InstalledSQLInstances
Create TABLE #InstalledSQLInstances (SQLInstance Varchar(255), [Value] varchar(255), [dummy] varchar(255))
INSERT #InstalledSQLInstances
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server', N'InstalledInstances'
UPDATE #InstalledSQLInstances SET SQLInstance = REPLACE(SQLInstance, 'InstalledInstances - Item #', '')


INSERT #SysInfo (Property, Value) Values ('SQL Service Status:', SPACE(0))
DECLARE @NumOfSQLSvr INT, @InstancesName varchar(255)
SELECT @NumOfSQLSvr = MAX(CONVERT(INT,SQLInstance)) FROM #InstalledSQLInstances
WHILE (@NumOfSQLSvr> 0)
BEGIN
      SELECT @InstancesName = [Value] FROM #InstalledSQLInstances WHERE SQLInstance = CONVERT(Varchar(10), @NumOfSQLSvr)
      SET @ServiceList = NULL
      IF @InstancesName = 'MSSQLSERVER'
            SELECT @ServiceList = COALESCE(@ServiceList + ' ', '') + LTRIM(Service) FROM #SCQuery WHERE Service LIKE '%sql%' AND Service NOT LIKE '%$%'
      ELSE
            SELECT @ServiceList = COALESCE(@ServiceList + ' ', '') + LTRIM(Service) FROM #SCQuery WHERE Service LIKE '%sql%' AND Service LIKE '%$'+@InstancesName+'%'
      INSERT #SysInfo (Property, Value)
      SELECT '                           ['+CONVERT(Varchar(10), @InstancesName)+']:', LEFT(@ServiceList, LEN(@ServiceList)-1)
--SELECT @ServiceList
      SET @NumOfSQLSvr = @NumOfSQLSvr - 1
END

SELECT Property, Value FROM #SysInfo ORDER BY ROWID

--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
--SQL Server Information
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////

IF (OBJECT_ID('tempdb..#SQLServerInfo') IS NOT NULL)
DROP TABLE #SQLServerInfo
CREATE TABLE #SQLServerInfo
(
      Property varchar(100) NULL,
      Value varchar(8000) NULL
)

--SET @sqldesc =
INSERT #SQLServerInfo
SELECT 'Current SQL Instance:' , CONVERT(Varchar(255), @@ServerName)
UNION ALL
SELECT 'SQL Server Version:',  CONVERT(Varchar(255),LEFT(@@Version,CHARINDEX('(',@@Version)-2))+ SPACE(1)+CONVERT(Varchar(255), SERVERPROPERTY('Edition'))+ SPACE(1)+Convert(Varchar(255),SERVERPROPERTY('ProductLevel'))
UNION ALL
SELECT 'SQL Server Property:','Collation ='+ CONVERT(Varchar(255),SERVERPROPERTY('Collation'))
+ ', IsClustered ='+ CONVERT(Varchar(255),SERVERPROPERTY('IsClustered'))
+ ', IsFullTextInstalled ='+ CONVERT(Varchar(255),SERVERPROPERTY('IsFullTextInstalled'))
+ ', IsIntegratedSecurityOnly ='+ CONVERT(Varchar(255),SERVERPROPERTY('IsIntegratedSecurityOnly'))
+ ', IsSingleUser ='+ CONVERT(Varchar(255),SERVERPROPERTY('IsSingleUser'))
+ ', MachineName ='+ CONVERT(Varchar(255),SERVERPROPERTY('MachineName'))


DECLARE @SqlDesc varchar(8000)

IF (CHARINDEX('9.00',CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')))>0)
      SELECT @SqlDesc = COALESCE(@SqlDesc + ', ', '') +[Name]+ '='+CONVERT(Varchar(100),value_in_use) FROM sys.configurations ORDER BY [Name]
ELSE
      SELECT @SqlDesc = COALESCE(@SqlDesc + ', ', '') +v.name+ '='+CONVERT(Varchar(100),r.value)
      FROM master.dbo.sysconfigures c, master..spt_values v, master.dbo.syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 and v.number = r.config and (c.status & 2 = 0 or exists (select * from master.dbo.syscurconfigs where config = 518 and value = 1)) order by v.name
INSERT #SQLServerInfo
SELECT 'SQL Server Configure:', @SqlDesc

--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
--Database Information
--///////////////////////////////////////////////////////////////////////////////////////////////////////////////
IF (OBJECT_ID('tempdb..#DatabaseInfo') IS NOT NULL)
DROP TABLE #DatabaseInfo
CREATE TABLE #DatabaseInfo
(
      Property varchar(100) NULL,
      Value varchar(8000) NULL,
      Value2 varchar(255) NULL,
      Value3 BIGINT NULL
)

DECLARE @exec_stmt Varchar(8000), @low nvarchar(11), @dbdesc varchar(8000)
DECLARE @dbname varchar(255), @MaxDBid INT,  @dbIdx INT

SELECT @MaxDBid = MAX(dbid), @dbIdx = MIN(dbid) FROM master.dbo.sysdatabases
WHILE @dbIdx <= @MaxDBid
BEGIN
      SELECT @dbname = name FROM master.dbo.sysdatabases WHERE dbid = @dbIdx
      INSERT #DatabaseInfo (Property, Value) SELECT 'Database Name:', UPPER(@dbname)

      -- These properties always available
      SELECT @dbdesc = 'Compatibility_level=' + CONVERT(varchar(10),cmptlevel) FROm master.dbo.sysdatabases WHERE dbid = @dbIdx
      SELECT @dbdesc = @dbdesc + ', Status=' + convert(sysname,DatabasePropertyEx(@dbname,'Status'))
      SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@dbname,'Updateability'))
      SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@dbname,'UserAccess'))
      SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@dbname,'Recovery'))
      SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@dbname,'Version'))

      -- These props only available if db not shutdown
      IF DatabaseProperty(@dbname, 'IsShutdown') = 0
      BEGIN
            SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@dbname,'Collation'))
            SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@dbname,'SQLSortOrder'))
      END

      -- These are the boolean properties
      IF DatabasePropertyEx(@dbname,'IsAutoClose') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
      IF DatabasePropertyEx(@dbname,'IsAutoShrink') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
      IF DatabasePropertyEx(@dbname,'IsInStandby') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
      IF DatabasePropertyEx(@dbname,'IsTornPageDetectionEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
      IF DatabasePropertyEx(@dbname,'IsAnsiNullDefault') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
      IF DatabasePropertyEx(@dbname,'IsAnsiNullsEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
      IF DatabasePropertyEx(@dbname,'IsAnsiPaddingEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
      IF DatabasePropertyEx(@dbname,'IsAnsiWarningsEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
      IF DatabasePropertyEx(@dbname,'IsArithmeticAbortEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
      IF DatabasePropertyEx(@dbname,'IsAutoCreateStatistics') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
      IF DatabasePropertyEx(@dbname,'IsAutoUpdateStatistics') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
      IF DatabasePropertyEx(@dbname,'IsCloseCursorsOnCommitEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
      IF DatabasePropertyEx(@dbname,'IsFullTextEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
      IF DatabasePropertyEx(@dbname,'IsLocalCursorsDefault') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
      IF DatabasePropertyEx(@dbname,'IsNullConcat') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
      IF DatabasePropertyEx(@dbname,'IsNumericRoundAbortEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
      IF DatabasePropertyEx(@dbname,'IsQuotedIdentifiersEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
      IF DatabasePropertyEx(@dbname,'IsRecursiveTriggersEnabled') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
      IF DatabasePropertyEx(@dbname,'IsMergePublished') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
      IF DatabasePropertyEx(@dbname,'IsPublished') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
      IF DatabasePropertyEx(@dbname,'IsSubscribed') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
      IF DatabasePropertyEx(@dbname,'IsSyncWithBackup') = 1
            SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'
      INSERT #DatabaseInfo (Property, Value,Value2) SELECT 'Database Property:', @dbdesc, @dbname
      INSERT #DatabaseInfo (Property, Value,Value2) SELECT 'Database Files:', SPACE(0), SPACE(0)
      --select 'File Group #'+ CONVERT(varchar(10), groupid)+' ('+RTRIM(name)+ '), Size='+ CONVERT(Varchar(10),ROUND(size* 8192/1048576,0))+' Mb'
      select @exec_stmt = 'select ''                           [''+ CASE LEN(CONVERT(varchar(10), f.fileid)) WHEN 1 THEN ''0''+CONVERT(varchar(10), f.fileid) ELSE CONVERT(varchar(10), f.fileid) END +'']: '', ''Name=''+RTRIM(f.name)+'', GroupName=''+ISNULL(g.groupname, ''N/A'')+ '', Size=''+CONVERT(Varchar(10),ROUND(CONVERT(BIGINT,f.size)* 8192/1024,0))+ '' KB, FileName=''+RTRIM(f.filename)+'', Growth=''+CONVERT(varchar(20),f.growth)+'', Maxzide=''+CASE maxsize WHEN -1 THEN ''Unlimited'' ELSE CONVERT(Varchar(10),f.maxsize) END +'', Usage=''+  CASE f.status & 0x40 when 0x40 then ''log only'' else ''Data Only'' END,'''+@dbname+''', ROUND(CONVERT(BIGINT,f.size)* 8192/1024,0) from '+ QUOTENAME(@dbname, N'[') + N'.dbo.sysfiles f LEFT OUTER JOIN '+ QUOTENAME(@dbname, N'[') + N'.dbo.sysfilegroups g ON g.groupid = f.groupid order by fileid'
      --PRINT @exec_stmt
      INSERT #DatabaseInfo (Property, Value, Value2, Value3)
      EXEC (@exec_stmt)

      UPDATE #DatabaseInfo SET Value = (SELECT 'Size='+CONVERT(VARCHAR(100), ROUND(SUM(Value3)* 8192/1024,0))+ ' KB, '  FROM #DatabaseInfo WHERE Value2 = @dbname and Value3 IS NOT NULL)+Value WHERE Value2 = @dbname AND Property = 'Database Property'

      SELECT @dbIdx = MIN(dbid) FROM master.dbo.sysdatabases WHERE dbid > @dbIdx
END

SELECT Property, Value FROM #SQLServerInfo
UNION ALL
SELECT Property, Value FROM #DatabaseInfo

IF (CHARINDEX('9.00',CONVERT(VARCHAR(100),SERVERPROPERTY('ProductVersion')))>0) AND (@CmdShellInUse =0x0)
BEGIN
      EXEC sp_configure 'xp_cmdshell', 0
      RECONFIGURE WITH OVERRIDE
END

SET ANSI_WARNINGS ON
SET NOCOUNT OFF
lcohanDatabase Analyst

Commented:
And of course you can run it against a SQL box by using SQLCMD utility at a command prompt if that's what you'd like.

Author

Commented:
Thanks bibi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial