Automating SQL Server instance and Database Inventory preparation

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 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


Posted in SQL Server DBA | Tagged , , , , | 1 Comment

Leave a Reply

1 Comment threads
0 Thread replies
Most reacted comment
Hottest comment thread
1 Comment authors
Shripad Recent comment authors
newest oldest most voted
Notify of

Nice scripts but if you give sample output for tables then it looks much better..