Metadata Management - Crawler Query - To get data bases info from a list of servers

To fetch list of databases in a server usually we use EXEC sp_helpdb, but if your IT infrastructure is scaled to multiple servers we need to collect this information from all the serves in given IT landscape. 

Usually in metadata management you capture this information in to a table to do your data analysis and reporting, you either create "Central Management Servers" to run your query at a single go in all these servers or create an SSIS data flow task using dynamic source connection manager to connect all these serves and pull information into your metadata tables.

In both cases a plain EXEC sp_helpdb might not help, you get the results in different windows for each server.

Use below piece of code to get the results in a single plain window or place this in your SSIS OLEDB Source - SQL Command option to pull and push this information straight into your underlying tables:

SET NOCOUNT ON

DECLARE @AllTables TABLE (

Name sysname,
db_size sysname,
owner sysname,
dbid sysname,
created sysname,
status nvarchar(max),
compatibility_level sysname)     

INSERT INTO @AllTables (Name,

db_size,
owner,
dbid,
created,
status,
compatibility_level)

EXEC sp_helpdb

SET NOCOUNT OFF

SELECT @@servername as ServerName, Name AS DataBaseName, 'UserDefined' AS DataBaseType, db_size AS DataBaseSize FROM @AllTables
WHERE dbid > 4

I am filtering out the results (skipping master tables and few database attributes) based on my need you can play around with them accordingly.

Happy Learning.. :)