Hi Friends,
Find enclosed here with a script which will capture most of the SQL Server Inventory Information. This script can work on SQL 2000, 2005 & 2008.
/*Script : Inventory_baseline.sqlAuthor : Gurpreet Singh Sethi & Abhay Chaudhary, 15 OCT, 2009Purpose : Collecting SQL Server 2000/2005/2008 baseline inventory: Requirements: SQL Server 2008 Express Edition (Please go through Inventory_baseline.txt)Suggestions :
gur.sethi@in.ibm.comVersion: 1.0*/
--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodvercreate table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8 begin
-- Step 2: This code will be used if the instance is Not SQL Server 2000
Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
Declare @ClusterName nvarchar(100)
Declare @ClusterResourceName nvarchar(100)
Declare @Registry_key4 nvarchar(100)
Declare @Registry_key5 nvarchar(100)
Declare @DependsOn nvarchar(100)
Declare @Address nvarchar(100)
Declare @OSProductName nvarchar(100)
Declare @OSVersion nvarchar(100)
Declare @Registry_key6 nvarchar(100)
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name
Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
Set @key=N'SYSTEM\CurrentControlSet\Services\'
+@instance_name1;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
If @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterName',@value=@ClusterName OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterNameResource',@value=@ClusterResourceName OUTPUT
SET @Registry_key4=N'Cluster\Resources\'+@ClusterResourceName; Print @ClusterResourceName
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key4,@value_name='DependsOn',@value=@DependsOn OUTPUT
SET @Registry_key5=N'Cluster\Resources\'+@DependsOn+'\Parameters';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key5,@value_name='Address',@value=@Address OUTPUT SET @Registry_key6=N'Software\Microsoft\Windows NT\CurrentVersion';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key6,@value_name='ProductName',@value=@OSProductName OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key6,@value_name='CSDVersion',@value=@OSVersion OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
Set @startuptype= (select 'Start Up Mode' = CASE WHEN @startup_type=2 then 'AUTOMATIC' WHEN @startup_type=3 then WHEN @startup_type=4 then 'Disabled' END)
--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.
Declare @ClusterNodeName nvarchar(400)
SELECT @ClusterNodeName = COALESCE(@ClusterNodeName+'' ,'') + Nodename from
sys.dm_os_cluster_nodes
--Step 5: Getting Drives names if the server is on cluster else this value will be NULL.
Declare @ClusterDriveName nvarchar(400)
SELECT @ClusterDriveName = COALESCE(@ClusterDriveName+'' ,'') + DriveName from sys.dm_io_cluster_shared_drives
-- Step 6: Printing Server details
SELECT @domain as 'Domain', serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName', @OSProductName + ' '+ @OSVersion 'OS Edn. Ver', CPU_COUNT as 'CPUCount', (physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB', @Ipaddress as 'IP_Address', @instance_name1 as 'SQL InstanceName', @image_path as 'BinariesPath', @log_directory as 'ErrorLogsLocation', @start_username as 'StartupUser', @Startuptype as 'StartupType', (select CASE WHEN SUBSTRING(Charcater_Value,1,1)=9 THEN 'SQL SERVER 2005'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) WHEN SUBSTRING(Charcater_Value,1,2)=10 THEN 'SQL SERVER 2008'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) END FROM PRODVER ) as 'SQL Edn. Ver.', serverproperty('productversion') as 'SQL Version', serverproperty('collation') as 'Collation', serverproperty('Isclustered') as 'ISClustered', @ClusterName as 'Cluster Name', @Address as 'Cluster Address', @ClusterNodeName as 'ClusterNodes', @ClusterDriveName as 'Cluster Drive Names', serverproperty('IsFullTextInstalled') as 'ISFullText' From sys.dm_os_sys_info
-- Step 7: Printing database details
SELECT serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine' ,@instance_name1 as InstanceName, (SELECT 'file_type' = CASE WHEN s.groupid <> 0 THEN 'data' WHEN s.groupid = 0 THEN 'log' END) AS 'fileType' , d.dbid as 'DBID' , d.name AS 'DBName' , s.name AS 'LogicalFileName' , s.filename AS 'PhysicalFileName' , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB , d.cmptlevel as 'CompatibilityLevel' , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel' , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' , --, d.is_published as 'Publisher' --, d.is_subscribed as 'Subscriber' --, d.is_distributor as 'Distributor' (SELECT 'is_replication' = CASE WHEN d.category = 1 THEN 'Published' WHEN d.category = 2 THEN 'subscribed' WHEN d.category = 4 THEN 'Merge published' WHEN d.category = 8 THEN 'merge subscribed' Else 'NO replication' END) AS 'Is_replication' , m.mirroring_state as 'MirroringState' --INTO master.[dbo].[databasedetails] FROM sys.sysdatabases d INNER JOIN sys.sysaltfiles s ON d.dbid=s.dbid INNER JOIN sys.database_mirroring m ON d.dbid=m.database_id ORDER BY d.name
--Step 8 :printing Backup details
Select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),e.database_name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery'))), (Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset a Where a.database_name=e.database_name and a.server_name = @@servername and type='D' Group by a.database_name) Database_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset b Where b.database_name=e.database_name and b.server_name = @@servername and type='L' Group by b.database_name) Log_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset c Where c.database_name=e.database_name and c.server_name = @@servername and type='I' Group by c.database_name) Diff_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset d Where d.database_name=e.database_name and d.server_name = @@servername and type='F' Group by d.database_name) File_Bk
From msdb.dbo.backupset e Where e.database_name Not in ('tempdb','pubs','northwind','model') and e.server_name = @@Servername and e.database_name in (Select Distinct name from master..sysdatabases)
-- DATABASES NEVER BACKED UP
Union all select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),NULL, NULL , NULL, NULL from master..sysdatabases as record where name not in (select distinct database_name from msdb.dbo.backupset) and name not in ('tempdb','pubs','northwind','model') order by 1,2
END
ELSE
BEGIN
--Step 9: If the instance is 2000 this code will be used.
-- declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400) set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15) set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)
SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'
Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)
Declare @OSProductName2 nvarchar(100)
Declare @OSVersion2 nvarchar(100)
Declare @Registry_key66 nvarchar(100)
Set @image_path1=null
Set @image_path2=null
Set @image_path3=null
Set @image_path4=null
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterName',@value=@ClusterName OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterResourceName',@value=@ClusterResourceName OUTPUT SET @Registry_key4=N'Cluster\Resources\'+@ClusterResourceName;
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key4,@value_name='DependsOn',@value=@DependsOn OUTPUT SET @Registry_key5=N'Cluster\Resources\'+@DependsOn+'\Parameter';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key5,@value_name='Address',@value=@Address OUTPUT
SET @Registry_key66=N'Software\Microsoft\Windows NT\CurrentVersion';
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key66,@value_name='ProductName',@value=@OSProductName2 OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key66,@value_name='CSDVersion',@value=@OSVersion2 OUTPUT
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)
declare @Out nvarchar(400)
declare @value nvarchar (20)
SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null
-- Step 10: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'
BEGIN set @system_instance_name=@instance_name set @instance_name='MSSQL$'+@instance_name
SET @key=N'SYSTEM\CurrentControlSet\Services\'
+@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
IF @instance_name='MSSQLSERVER' BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\'
+@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters'; SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
set @startuptype= (select 'Start Up Mode' = CASE WHEN @startup_type=2 then 'AUTOMATIC' WHEN @startup_type=3 then 'MANUAL' WHEN @startup_type=4 then 'Disabled' END)
--Step 11 : Using ipconfig and xp_msver to get physical memory and IP
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value)
exec xp_msver PhysicalMemory
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)
exec xp_cmdshell 'ipconfig' delete from ipadd where ip not like '%IP Address.%' or IP is null
-- Step 12 : Getting the Server details
SELECT top 1 @domain as 'Domain', serverproperty('Machinename') as 'MachineName', @OSProductName2 + ' '+ @OSVersion2 'OS Edn. Ver', @CPU as 'CPUCount', cast (t.internal_value as bigint) as PhysicalMemoryMB, cast(substring ( I.IP , 40,45) as nvarchar(20))as IP_Address, serverproperty('Instancename') as 'InstanceName', @image_path as 'BinariesPath', @log_directory as 'ErrorLogsLocation', @start_username as 'StartupUser', @Startuptype as 'StartupType', (select CASE WHEN SUBSTRING(Charcater_Value,1,1)=8 THEN 'SQL SERVER 2000'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) END FROM PRODVER ) as 'SQL Edn. Ver.', serverproperty('productversion') as 'Version', serverproperty('collation') as 'Collation', @ClusterName as 'Cluster Name', @Address as 'Cluster Address', serverproperty('Isclustered') as 'ISClustered', @Out as 'ClustreNodes', serverproperty('IsFullTextInstalled') as 'ISFullText' From tmp t inner join IPAdd I on t.server = I.server
-- Step 13 : Getting the instance details
SELECT serverproperty ('Machinename') as 'Machine', serverproperty ('Instancename') as 'InstanceName', (SELECT 'file_type' = CASE WHEN s.groupid <> 0 THEN 'data' WHEN s.groupid = 0 THEN 'log' END) AS 'fileType' , d.dbid as 'DBID' , d.name AS 'DBName' , s.name AS 'LogicalFileName' , s.filename AS 'PhysicalFileName' , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB ,d.cmptlevel as 'CompatibilityLevel' , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel' , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' , (SELECT 'is_replication' = CASE WHEN d.category = 1 THEN 'Published' WHEN d.category = 2 THEN 'subscribed' WHEN d.category = 4 THEN 'Merge published' WHEN d.category = 8 THEN 'merge subscribed' Else 'NO replication' END) AS 'Is_replication', @Mirroring as 'MirroringState' FROM sysdatabases d INNER JOIN sysaltfiles s ON d.dbid=s.dbid ORDER BY d.name
-- Step 14 : Getting backup details
Select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),e.database_name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery'))), (Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset a Where a.database_name=e.database_name and a.server_name = @@servername and type='D' Group by a.database_name) Database_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset b Where b.database_name=e.database_name and b.server_name = @@servername and type='L' Group by b.database_name) Log_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset c Where c.database_name=e.database_name and c.server_name = @@servername and type='I' Group by c.database_name) Diff_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset d Where d.database_name=e.database_name and d.server_name = @@servername and type='F' Group by d.database_name) File_Bk
From msdb.dbo.backupset e Where e.database_name Not in ('tempdb','pubs','northwind','model') and e.server_name = @@Servername and e.database_name in (Select Distinct name from master..sysdatabases)
-- NEVER BACKED UP
Union allselect Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),NULL, NULL , NULL, NULLfrom master..sysdatabases as recordwhere name not in (select distinct database_name from msdb.dbo.backupset)and name not in ('tempdb','pubs','northwind','model')order by 1,2
-- Step 15: Dropping the table we created for IP and Physical memory
Drop Table TMP
Drop Table IPADD
Drop Table Nodes
Drop Table Prodver
end
go
-- Step 16 : Setting Nulls and Quoted identifier back to Off
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
