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

SQL Script to Split a String

I wondered to see the shortest way to split a string using T-SQL code. Usually we need to use a user defined function to split string but we can use one of the T-SQL function when the string contains 4 or less than 4 words.

Here is the SQL Script to split a string

Syntax: SELECT PARSENAME (REPLACE(String, Delimiter, ‘.’), Word_Number)

Example:

DECLARE @String VARCHAR(100)

SET @String =’SQL Server 2008 R2′

— Here we find 8 words with Delimiter as space (‘ ‘)

— Now Split the string

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 1)

Output: R2

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 2)

Output: 2008

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 3)

Output: Server

SELECT PARSENAME(REPLACE(@String,’ ‘,’.’), 4)

Output:

SQL

Note: As we knows that the function Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name. So we can use it when the string contains words <=4.

udayarumilli.com

Ctrl N short cut is not working in SQL 2008

Ctrl N short cut is not working in SQL 2008