It’s really been a long time. Yah recently we @ our organization are requested to automate the process of building server and database inventory. We came up with the script which can capture sever / Instance / Database details from all the servers that are existed in a domain. Since we are strictly restricted to do not use the linked servers looked for the next option “Accessing System Registry values”. No luck we miss either 64-bit instances or 32-bit instances or if the installation is not done properly. Finally we could find the way to build the inventory using “XP_CMDSHELL”.
For the future purpose and to create essential reports as well we are going to store the inventory information in relational objects. Here is the object structure.
Script for creating Tables
CREATE TABLE Environment (
ID TINYINT IDENTITY PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Deleted BIT NOT NULL DEFAULT(0))
CREATE TABLE Servers (
ID SMALLINT IDENTITY PRIMARY KEY,
Server_Name VARCHAR(100) NOT NULL,
OS_Version VARCHAR(100),
EnvironmentID TINYINT REFERENCES Environment(ID),
IPAddress VARCHAR(20),
Deleted BIT NOT NULL DEFAULT(0))
CREATE TABLE InstanceInventory (
ID SMALLINT IDENTITY PRIMARY KEY,
ServerID SMALLINT REFERENCES Servers (ID) NOT NULL,
InstanceName VARCHAR(100)NOT NULL,
Version VARCHAR(100) NULL,
Edition VARCHAR(100) NULL,
Deleted BIT NOT NULL DEFAULT(0))
CREATE TABLE DBInventory(
ID SMALLINT IDENTITY,
InstanceInventoryID SMALLINT REFERENCES InstanceInventory (ID)NOT NULL,
DBName VARCHAR(100) NOT NULL,
CompatabilityLevel TINYINT NULL,
Deleted BIT NOT NULL DEFAULT(0))
Script to Creating Stored procedures
Now we are going to create two stored procedures. “usp_Inventory_Instances” and“usp_Inventory_Databases”
———————————————-
———————————————-
GETTING INSTANCE DETAILS FOR ENVIRONMENT MAP———————————————-
———————————————-
CREATE PROC usp_Inventory_Instances(@ServerName VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
DECLARE @Instances TABLE(ServerName VARCHAR(100))
DECLARE @MachedInstances TABLE (ID INT IDENTITY,ServerName VARCHAR(50))
DECLARE @Counter SMALLINT
DECLARE @I SMALLINT
DECLARE @InstanceName VARCHAR(50)
DECLARE @ServerID SMALLINT
DECLARE @InstanceID SMALLINT
SET @ServerID=0
SELECT @ServerID=ID FROM Servers WHERE Server_Name=@ServerName
IF (@ServerID=0)
BEGIN
RETURN 0
END
INSERT INTO @Instances EXEC XP_CMDSHELL ‘SQLCMD -L’
INSERT INTO @MachedInstances (ServerName) SELECT LTRIM(RTRIM(ServerName)) FROM @Instances WHERE ServerName LIKE ‘%’+@ServerName+’%’
–SELECT * FROM @MachedInstances
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
IF NOT EXISTS(SELECT 1 FROM InstanceInventory WHERE ServerID=@ServerID AND InstanceName=@InstanceName)
BEGIN
INSERT INTO InstanceInventory (ServerID,InstanceName) VALUES(@ServerID,@InstanceName)
SET @InstanceID=SCOPE_IDENTITY()
EXEC usp_Inventory_Databases @InstanceID
END
ELSE
BEGIN
SELECT ‘Instance Details are already exists in our database’
END
SET @i=@i+1
END
UPDATE INSTANCEINVENTORY SET DELETED=1 WHERE ID NOT IN(SELECT INSTANCEINVENTORYID FROM DBINVENTORY)
PRINT ‘All instances and respective database details Inserted Successfully’
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN 0
END CATCH
COMMIT
END
———————————————-
———————————————-
GETTING DATABASE DETAILS FOR ENVIRONMENT MAP———————————————-
———————————————-
CREATE PROC usp_Inventory_Databases(@InstanceID SMALLINT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Databases TABLE(Name VARCHAR(1000))
DECLARE @Databases1 TABLE(ID INT IDENTITY,Name VARCHAR(100))
DECLARE @String VARCHAR(200)
DECLARE @InstanceName VARCHAR(100)
DECLARE @Count TINYINT
DECLARE @i TINYINT
DECLARE @DBName VARCHAR(1000)
SELECT @InstanceName=InstanceName FROM InstanceInventory WHERE ID=@InstanceID
SET @String=’SQLCMD -S ‘+@InstanceName+’ -q “SET NOCOUNT ON;USE MASTER;SELECT Name FROM SYSDATABASES”‘
INSERT INTO @Databases EXEC XP_CMDSHELL @String
IF EXISTS(SELECT 1 FROM @Databases WHERE Name LIKE’%Sqlcmd:%Error%’ OR Name LIKE ‘%HResult%Level%’ OR Name LIKE ‘%Login failed%’)
BEGIN
RETURN 0
END
DELETE @Databases WHERE Name=’Name’ OR Name LIKE ‘%——-%’ OR Name IS NULL OR Name IN(‘master’,’tempdb’,’model’,’msdb’) OR Name LIKE ‘%Changed database context%’
UPDATE @Databases SET Name=LTRIM(RTRIM(Name))
INSERT INTO @Databases1 (Name) SELECT Name FROM @Databases
SET @i=1
SELECT @Count=COUNT(1) FROM @Databases1
WHILE(@Count>=@i)
BEGIN
SELECT @DBName=Name FROM @Databases1 WHERE ID=@i
INSERT INTO DBInventory (InstanceInventoryID,DBName) VALUES(@InstanceID,@DBName)
SET @i=@i+1
END
END
Executing Stored Procs:
Now run the below statement.EXEC usp_Inventory_Instances ‘NameofTheServer’
Now you will be able to find the existing instance details from the table “InstanceInventory” and the corresponding databases details stored on “DBInventory”
I have tried this but didn’t work
Its not working for me aswell