SELECT@@servername as servername,db_name() as databasename,       s.name AS schemaname,t.name AS tablename,    p.rows AS rowcounts,    SUM(a.total_pages) * 8 AS totalspaceKB,    SUM(a.used_pages) * 8 AS usedspaceKB,    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unusedspaceKB,getdate() as captureddatetimeFROM sys.tables tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.NAME NOT LIKE 'dt%'    AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255--and t.name =''XXXX'' ---- replace the XXXX with table nameGROUP BYt.name, s.name, p.Rows

-- create table with only the names of databases that are publishedSELECTname as databasenameINTO #alldatabasesFROM sys.databases WHERE database_id > 4CREATE TABLE #alltablesizes(servername sysname,databasename sysname,schemaName sysname,tablename sysname,rowcounts INT,totalspaceKB DECIMAL(18,2),usedspaceKB DECIMAL(18,2),unusedspaceKB DECIMAL(18,2),captureddatetime datetime  );DECLARE @command VARCHAR(MAX);-- run the below code to get table count from all the databasesSET @command = 'USE [?]IF DB_NAME() IN (SELECT databasename FROM #alldatabases)BEGININSERT #alltablesizesSELECT@@servername as servername,db_name() as databasename,       s.name AS schemaname,t.name AS tablename,    p.rows AS rowcounts,    SUM(a.total_pages) * 8 AS totalspaceKB,    SUM(a.used_pages) * 8 AS usedspaceKB,    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unusedspaceKB,getdate() as captureddatetimeFROM sys.tables tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.NAME NOT LIKE ''dt%''    AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255--and t.name =''XXXX'' ---- replace the XXXX with table nameGROUP BYt.name, s.name, p.RowsEND';EXEC sp_MSforeachdb @commandselect * from #alltablesizesorder by 5 descdrop table #alltablesizesdrop table #alldatabases

SELECTname as databasenameINTO #alldatabasesFROM sys.databases WHERE database_id > 4CREATE TABLE #alltablesizes(servername sysname,databasename sysname,schemaName sysname,tablename sysname,rowcounts INT,totalspaceKB DECIMAL(18,2),usedspaceKB DECIMAL(18,2),unusedspaceKB DECIMAL(18,2),captureddatetime datetime);DECLARE @command VARCHAR(MAX);-- run the below code to get table count from all the databasesSET @command = 'USE [?]IF DB_NAME() IN (SELECT databasename FROM #alldatabases)BEGININSERT #alltablesizesSELECT@@servername as servername,db_name() as databasename,s.name AS schemaname,t.name AS tablename,p.rows AS rowcounts,SUM(a.total_pages) * 8 AS totalspaceKB,SUM(a.used_pages) * 8 AS usedspaceKB,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS unusedspaceKB,getdate() as captureddatetimeFROM sys.tables tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.NAME NOT LIKE ''dt%''AND t.is_ms_shipped = 0AND i.OBJECT_ID > 255--and t.name =''XXXX'' ---- replace the XXXX with table nameGROUP BYt.name, s.name, p.RowsEND';EXEC sp_MSforeachdb @commandselect servername,databasename,captureddatetime,sum(cast(rowcounts as bigint)) from #alltablesizesgroup by servername,databasename,captureddatetimeorder by 1,2drop table #alltablesizesdrop table #alldatabases