Create System Proc to Grab Row Counts for Tables

,

Really basic way to help identify tables that have 0 rows, etc. It uses the sp_MSForEachTable to crawl the database.

Very simple script.

USE MASTER
GO 
IF OBJECT_ID('dbo.sp_DBTablesCountRows','p') IS NOT NULL
	BEGIN
		DROP PROCEDURE dbo.sp_DBTablesCountRows
	END  
GO
CREATE PROCEDURE dbo.sp_DBTablesCountRows
	AS
-- USED to display simple Count of Rows from Tables in a given DB 
-- sp_ (System Proceedure) is used to make the proc available to all DB's from Master
-- 12-1-2006 JCD 
-- USAGE: 
--		EXEC sp_msForeachDB @command1="USE ?;exec sp_DBTablesCountRows"
--		sp_DBTablesCountRows -- Run from Specific Database 

	DECLARE @dbName as varchar(150)
	SELECT @dbName=db_name()

	BEGIN 
		IF @dbName NOT IN ('Master','MSDB','TEMPDB', 'MODEL')
			BEGIN 
				PRINT UPPER(@dbName) + ': DATABASE TABLES ROW COUNTS'
				PRINT '========================================='
				PRINT ''
				 EXECUTE sp_msforeachtable @command1="IF (PATINDEX('%#%','?')<1 or LEN('?')>128) BEGIN Select count(1) as [?]  from ? (NOLOCK) END;"
				PRINT ''
			END
	END
GO 
-- Sample Execution for All DB's on a server
EXEC sp_msForeachDB @command1="USE ?;exec sp_DBTablesCountRows"

Rate

Share

Share

Rate