Sometime you might want to get row counts for all the tables in the entire SQL server. It might help you to identify large tables and tables have no records. Following script will help you to Get row count of all user tables in all databases in current SQL Server Instance.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SET NOCOUNT ON DECLARE @AllTables table (ServerName nvarchar(200),DatabseName nvarchar(200),SchemaName nvarchar(200),TablerName nvarchar(200),Createdate datetime,Modifydate datetime,[RowCount] int) INSERT INTO @AllTables (ServerName,DatabseName,SchemaName,TablerName,Createdate,Modifydate,[RowCount]) EXEC sp_MSforeachdb ' SELECT @@SERVERNAME as [Servername] ,''?'' AS [Databsename] ,QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) AS [SCHEMANAME] ,QUOTENAME(sOBJ.name) AS [TableName] ,sOBJ.create_date AS [Createdate] ,sOBJ.modify_date AS [Modifydate] ,SUM(sPTN.Rows) AS [RowCount] FROM [?].sys.objects AS sOBJ INNER JOIN [?].sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = ''U'' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 GROUP BY sOBJ.schema_id ,sOBJ.create_date ,sOBJ.modify_date ,sOBJ.name ' SET NOCOUNT OFF SELECT * FROM @AllTables WHERE Databsename <> 'tempdb' ----Exclude temporary tables ORDER BY Servername,Databsename,SCHEMANAME,Tablername |
Leave a Comment