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

1
Leave a Reply

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

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