2008-06-02

COOL SP: Mass count - get count(*) from multiple tables with table name filtering

This is first of my COOL SP :)
It will get U list of table name with number of rows in that table.
It has a table name filtering based on T-SQL's LIKE key-word.
It uses dynamic SQL feature (sp_executeSql) to execute query concated in first select statment

Here it is:

IF object_id(N'dbo.TECH_MassCount', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_MassCount
GO

CREATE PROCEDURE dbo.TECH_MassCount
@filter varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
SET @s = ''

SELECT @s = @s + 'SELECT '''+table_name +''' as tabName, COUNT(*) as cnt FROM '+table_name+' UNION ALL '
FROM information_schema.tables
WHERE @filter = '' OR table_name LIKE @filter
ORDER BY table_name

IF ISNULL(@s, '')=''
RETURN -1
SET @s = LEFT(@s, LEN(@s)-11)
EXEC sp_executeSql @s
END
GO

How to use it:
EXEC TECH_MassCount
OR
EXEC TECH_MassCount 'tablename%' -- if U wont to filter output, and speed up query

Now few words about the SP.

No comments: