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 , , , , | Leave a comment

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz