Automating SQL Server instance and Database Inventory preparation

In our environment we have a requirement for Automating SQL Server instance and Database Inventory preparation. We tried different methods and came-up with the T-SQL scripts that helped us in creating the environment map. This script will capture the internal sql server details and stores on a centralized sql server database from where we can build a SSRS report that showcases the database environment map.

We need to create tables and corresponding procedures. Table creation:

Environment: Store data about available environments. Ex: Dev, Test, Prod etc
Servers: Host information Ex: Windows servers
Instance_Inventory: SQL Server instance details
DB Inventory: Database information on each instance

Script for Table Creation:

--Script to create Inventory Tables:

/****** Object:  Table [dbo].[Environment]    Script Date: 01/04/2011 06:30:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Environment](
    [ID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](30) NOT NULL,
    [Deleted] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH(PAD_INDEX  =OFF,STATISTICS_NORECOMPUTE  =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]
)ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Servers]    Script Date: 01/04/2011 06:30:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Servers](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [Server_Name] [varchar](100) NOT NULL,
    [OS_Version] [varchar](100) NULL,
    [EnvironmentID] [tinyint] NULL,
    [IPAddress] [varchar](20) NULL,
    [Deleted] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH(PAD_INDEX  =OFF,STATISTICS_NORECOMPUTE  =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[InstanceInventory]    Script Date: 01/04/2011 06:30:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[InstanceInventory](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [ServerID] [smallint] NOT NULL,
    [InstanceName] [varchar](100) NOT NULL,
    [Version] [varchar](100) NULL,
    [Edition] [varchar](100) NULL,
    [InsertedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [Disabled] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH(PAD_INDEX  =OFF,STATISTICS_NORECOMPUTE  =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DBInventory]    Script Date: 01/04/2011 06:30:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBInventory](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [InstanceInventoryID] [smallint] NOT NULL,
    [DBName] [varchar](100) NOT NULL,
    [CompatabilityLevel] [tinyint] NULL,
    [InsertedOn] [datetime] NOT NULL DEFAULT (getdate()),
    [DBSize] [varchar](50) NULL
)ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK__DBInvento__Insta__173876EA]    Script Date: 01/04/2011 06:30:20 ******/
ALTER TABLE [dbo].[DBInventory]  WITH CHECK ADD FOREIGN KEY([InstanceInventoryID])
REFERENCES [dbo].[InstanceInventory] ([ID])
GO
/****** Object:  ForeignKey [FK__InstanceI__Serve__145C0A3F]    Script Date: 01/04/2011 06:30:25 ******/
ALTER TABLE [dbo].[InstanceInventory]  WITH CHECK ADD FOREIGN KEY([ServerID])
REFERENCES [dbo].[Servers] ([ID])
GO
/****** Object:  ForeignKey [FK__Servers__Environ__108B795B]    Script Date: 01/04/2011 06:30:30 ******/
ALTER TABLE [dbo].[Servers]  WITH CHECK ADD FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[Environment] ([ID])
GO


--Login Script toCreate a uniqueloginonall the Servers:


SET NOCOUNT ON

DECLARE @exec_stmt NVARCHAR(625)
DECLARE @name      SYSNAME

USE MASTER

IF NOT EXISTS(SELECT 1 FROM SYSLOGINS WHERE [name]='Dba_maintenance')
BEGIN
EXEC sp_addlogin Dba_maintenance,testpwd@1
PRINT'LoginId: Dba_maintenance created'
END

CREATE TABLE #spdbdesc
(
 dbname sysname,
)

INSERT INTO #spdbdesc (dbname)
  SELECT NAME FROM master.dbo.sysdatabases


DECLARE ms_crs_c1 cursor global for
SELECT dbname from #spdbdesc
OPEN ms_crs_c1
FETCH ms_crs_c1 INTO @name
WHILE @@FETCH_STATUS <> 0
BEGIN
    SELECT @exec_stmt = 'use '+@name+Char(10)+' if not exists(select 1 from sysusers where name=''Dba_maintenance'')'+Char(10)+' begin'+Char(10)+'

EXEC sp_adduser ''Dba_maintenance'',''Dba_maintenance'''+Char(10)+' print ''User created at '+@name+''''+Char(10)+'end'

EXECUTE(@exec_stmt)
FETCH ms_crs_c1 INTO @name
END

DEALLOCATE ms_crs_c1   

DROP TABLE #spdbdesc

Script for Stored Procedure Creation:

Usp_Inventory_Databases: To capture database information for the given inventory

Usp_Inventory_Instances: To capture instance information from the network

GO

------------------------------------------------------------------------------------------------      
------------------------------------------------------------------------------------------------      
--GETTING DFATABASE DETAILS FOR THE GIVEN INSTANCE
--Parameter @InstanceID - ID column from InstanceInventory
------------------------------------------------------------------------------------------------      
------------------------------------------------------------------------------------------------      
CREATE PROC usp_Inventory_Databases(@InstanceID SMALLINT)
AS
BEGIN
SET NOCOUNT ON

/*** DECLARATION SECTION ***/
DECLARE @InstanceName VARCHAR(100)    
SELECT @InstanceName=InstanceName FROM InstanceInventory WHERE ID=@InstanceID    
DECLARE @String VARCHAR(1000)
DECLARE @Low NVARCHAR(11)
DECLARE @USER VARCHAR(50)
DECLARE @PWD VARCHAR(10)
DECLARE @i TINYINT
DECLARE @Count TINYINT
DECLARE @DBName VARCHAR(100)
DECLARE @Edition TABLE(Version VARCHAR(50),Edition VARCHAR(50))

/*** Assigning UserID and PWD to the local variables ***/

SET @USER='Dba_maintenance'
SET @PWD='RnMqp@1'

/*** Updates the Edition and Version information for the given instance ***/

SET @String =' SELECT Version,Edition from OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER='+@InstanceName+';UID='+'Dba_maintenance'+';PWD='+'RnMqp@1'+''','' SELECT LEFT(@@VERSION,38) AS Version,CAST(SERVERPROPERTY(''''productlevel'''') AS VARCHAR) + '''' ('''' + CAST(SERVERPROPERTY(''''edition'''') AS VARCHAR) + '''')'''' AS Edition'')'

INSERT INTO @Edition (Version,Edition)Execute(@String)

UPDATE InstanceInventory
SET
Version=(SELECT Version FROM @Edition),
Edition=(SELECT Edition FROM @Edition) WHERE ID=@InstanceID

SET @String=''

/*** Captures the database_name and db compatibility level into #Databases ***/

IF OBJECT_ID('tempdb..#Databases') IS NULL
BEGIN
    CREATE TABLE #Databases (ID INT IDENTITY,Name VARCHAR(100),CmptLevel TINYINT,DBSize VARCHAR(50),State INT )    
END

SET @String =' INSERT INTO #Databases (Name,Cmptlevel) SELECT Name, Cmptlevel from OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER='+@InstanceName+';UID='+@User+';PWD='+@PWD+''', master.dbo.sysdatabases )'

Execute(@String)
SET @STRING=''

/*** Calculates the Database size and updates at #Databases ***/
/*** While updating details first it verifies the state of the database. ***/
/*** If it is not accessable it skips that db and continue to the next DB ***/

SELECT @Low =CONVERT(VARCHAR(11),Low)FROM Master.dbo.spt_values WHEREtype= N'E'and number = 1    
SELECT @Count=COUNT(1)FROM #Databases
SET @i=1
SET @String=''
WHILE(@Count<=@i)
BEGIN
    SELECT @DBName=Name FROM #Databases WHERE ID=@i
    SET @String= 'UPDATE #Databases
        SET State= (SELECT S FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER='+@InstanceName+';UID='+@User+';PWD='+@PWD+''',''SELECT has_dbaccess('''''+@DBName+''''') As S'')) WHERE ID='+CONVERT(VARCHAR,@i)

    EXECUTE(@String)
    SET @STRING=''
    IF((SELECT State FROM #Databases WHERE ID=@i)=1)
    BEGIN
        SET @String= 'UPDATE #Databases
        SET DBSize = (SELECT STR(CONVERT(dec(15),sum(size))* '+ @Low +'/ 1048576,10,2)+ N'' MB'' FROM OPENROWSET(''MSDASQL'',''DRIVER={SQL Server};SERVER='+@InstanceName+';UID='+@User+';PWD='+@PWD+''','
         +QUOTENAME(@DBName, N'[')+ N'.dbo.sysfiles)) WHERE ID='+CONVERT(VARCHAR,@i)+' AND State=1'
  
        EXECUTE(@String)
    END
    SET @i=@i+1
END

/*** Inserts the Database details into ***/

INSERT INTO DBInventory (InstanceInventoryID,DBName,CompatabilityLevel,DBSize)SELECT @InstanceID,Name,CmptLevel,DBSize FROM #Databases
DROP TABLE #Databases

END

GO
------------------------------------------------------------------------------------------------      
------------------------------------------------------------------------------------------------      
/*** GETTING INSTANCE DETAILS FOR THE GIVEN SERVERNAME ***/
/*** Parameter - @ServerName***/
------------------------------------------------------------------------------------------------      
------------------------------------------------------------------------------------------------      
       
CREATE PROC usp_Inventory_Instances(@ServerName VARCHAR(50))      
AS      
BEGIN      
SET NOCOUNT ON      

/*** DECLARATION SECTION ***/

DECLARE @Instances TABLE(ServerName VARCHAR(100))      
DECLARE @MachedInstances TABLE(ID INT IDENTITY,ServerName VARCHAR(50))      
DECLARE @TestConnection TABLE(Name VARCHAR(1000))
DECLARE @Counter SMALLINT      
DECLARE @I SMALLINT      
DECLARE @InstanceName VARCHAR(50)      
DECLARE @ServerID SMALLINT      
DECLARE @InstanceID SMALLINT 
DECLARE @String VARCHAR(1000)

/*** Checking the given server name againest the existing server list ***/
/*** If the name is not avilable it exit the process***/

SET @ServerID=0      
SELECT @ServerID=ID FROM Servers WHERE Server_Name=@ServerName      
IF(@ServerID=0)      
BEGIN      
RETURN 0      
END 
      
/*** Captures all the existing servers into tab variable @Instances ***/
/*** Get all the related server/instance names by maching the given servername***/
/*** A loop starts to get each individual instance details***/
/*** First it inserts the instancename and assigns the Identity value to @InstanceID ***/
/*** It test the connection using the unique userID and PWD***/
/*** If it is not able to connect to that instance it simply updates the column Disabled as 1***/
/*** If the instance is accessable calls the sp usp_Inventory_Databases by passing the instanceID***/
/*** That sp get all the database details and insert into DBInventory table ***/
/*** Loop continues till the last instance***/

INSERT INTO @Instances EXECXP_CMDSHELL'SQLCMD -L';      
INSERT INTO @MachedInstances (ServerName) SELECT LTRIM(RTRIM(ServerName))FROM @Instances WHERE ServerName LIKE'%'+@ServerName+'%'      
SELECT @Counter=COUNT(1)FROM @MachedInstances      
SET @i=1      
BEGIN TRAN
BEGIN TRY
WHILE(@Counter<=@i)      
BEGIN
SELECT @InstanceName=ServerName FROM @MachedInstances WHERE ID=@i      
  INSERT INTO InstanceInventory (ServerID,InstanceName) VALUES(@ServerID,@InstanceName)      
  SET @InstanceID=SCOPE_IDENTITY()
  SET @String='SQLCMD -S '+@InstanceName+' -U Dba_maintenance -P RnMqp@1 -q "USE MASTER;"'    
  DELETE @TestConnection
  INSERT INTO @TestConnection EXEC XP_CMDSHELL @String    
  IF EXISTS(SELECT 1 FROM @TestConnection WHERE Name LIKE'%Sqlcmd:%Error%' OR Name LIKE '%HResult%Level%' OR Name LIKE '%Login failed%')    
  BEGIN    
    UPDATE INSTANCEINVENTORY SET Disabled=1 WHERE ID=@InstanceID  
  END
  ELSE
  BEGIN
    EXEC usp_Inventory_Databases @InstanceID
  END
  SET @i=@i+1
  PRINT 'Instance :'+@InstanceName+' Inserted '
END      
END TRY      
BEGIN CATCH      
ROLLBACK TRAN      
PRINT 'Rollbacked'  
RETURN 0      
END CATCH      
COMMIT 
PRINT'All instances and respective database details Inserted Successfully'       
END