Labels

Find row count for all tables for all databases in server

Today one of my cullig requested the script to find row count for all tables in all databases in the server. He used some scripts which was giving wrong data.

I already have 2 scripts available
  1. To find row count on all databases (click here)
  2. To find database space of all database in the server (click here)

I merge the scripts and output is working fine

I also updated the index for all scripts 

/*******************Script ********************************/
Drop TABLE #TableRowCounts
Go
create TABLE #TableRowCounts ([databaseNAme] Varchar(100),[TableName] VARCHAR(128),[Indexname]VARCHAR(128), [RowCount] INT) ;
Go
INSERT INTO #TableRowCounts ([databaseName],[TableName],[Indexname], [RowCount])
exec sp_msforeachdb
'use [?];

SELECT DB_NAME(),
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts

FROM
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id

GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY RowCounts '
Go
Select * From #TableRowCounts where databasename not in  ('master','model','msdb','tempdb','dbaadmin')
/***************************************************/


No comments:
Write comments

Please do not enter spam links

Services

More Services