Technical Article

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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating