Metadata Management - Crawler Query - To get complete tables and views info from a list of servers

To fetch list of tables in a server usually we use sys.tables, 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 select * from sys.tables 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 (

ServerName sysname,
DataContainerName sysname,
SchemaName sysname,
EntityName sysname,
EntityType sysname,
EntityCreatedDate sysname,
EntityUpdatedDate sysname,
EntitySizeInKB sysname,
EntityRowCount sysname)

DECLARE @SQLTemplate nvarchar(1500)

SET @SQLTemplate=

N' SELECT @@SERVERNAME AS Servername'
+ N', ''?'' AS DATABASE_NAME'
+ N', B.Name AS TABLE_SCHEMA'
+ N', A.Name AS TABLE_NAME'
+ N', A.Type_Desc AS TABLE_TYPE'
+ N', A.Create_Date AS CREATED_DATE'
+ N', A.Modify_Date AS MODIFIED_DATE'
+ N', ISNULL(SUM(E.used_pages)*8096/1024,0) AS Table_Size'
+ N', sum(case when (D.index_id < 2) and (E.type = 1) then D.rows else 0 end) AS Table_RowCount FROM [?].sys.objects A'
+ N' INNER JOIN [?].sys.schemas B'
+ N' ON A.schema_id=B.schema_id AND B.Name NOT IN (''temp'',''tmp'')'
+ N' LEFT JOIN [?].sys.tables C'
+ N' ON A.object_id = C.object_id'
+ N' LEFT JOIN [?].sys.partitions D'
+ N' ON D.object_id = C.object_id'
+ N' LEFT JOIN [?].sys.allocation_units E'
+ N' ON D.partition_id = E.container_id WHERE A.type IN (''U'',''V'')'
+ N' GROUP BY C.Name, B.Name, A.name,A.Type_Desc, A.Create_Date, A.Modify_Date'
+ N' ORDER BY A.name;'

INSERT INTO @AllTables (ServerName ,

DataContainerName ,
SchemaName ,
EntityName ,
EntityType ,
EntityCreatedDate ,
EntityUpdatedDate,
EntitySizeInKB,
EntityRowCount)

EXEC sp_msforeachdb @SQLTemplate

SET NOCOUNT OFF

SELECT * FROM @AllTables
WHERE DataContainerName NOT IN ('master','model','msdb','tempdb')
ORDER BY DataContainerName, SchemaName , EntityName

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

Happy Learning.. :)