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:
Post a Comment