Finding All empty tables in SQL db

Posted on August 17th, 2012 by alfredo

Had to do this at work figured this script might be useful to some around here. If you are and tester and gets a new system is a good way to find out what tables are getting no activity.

DECLARE @Tables TABLE
(
Name nvarchar(100)
)
DECLARE @EmptyTables TABLE
(
Name nvarchar(100)
)
DECLARE @AlmostNoRowsTables TABLE
(
Name nvarchar(100)
)

INSERT INTO @Tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

DECLARE @controlVariable nvarchar(100)
DECLARE @counter int
DECLARE @rowsleft int
SELECT Top 1 @controlVariable = Name FROM @Tables

Declare @SQL nVarChar(1000)
Declare @ex nVarChar(1000)
SELECT @SQL = ‘SELECT @counter=COUNT(*) FROM ‘

WHILE(@rowsleft > 0)
BEGIN
SELECT @ex = @SQL + @controlVariable

EXEC SP_EXECUTESQL @ex,N’@counter INT OUTPUT’,@counter OUTPUT

if(@counter = 0)
BEGIN
SELECT ‘Empty Table’ +@controlVariable
END

if(@counter > 0 AND @counter <10)
BEGIN
SELECT ‘Table has almost no rows’ +@controlVariable
END
DELETE @Tables WHERE Name = @controlVariable
SELECT Top 1 @controlVariable = Name FROM @Tables
SELECT @rowsleft = COUNT(*) FROM @Tables
PRINT @rowsleft
END

Tags: programming, sql

Categories: Programming, Programming tutorial, sql

Leave a Reply

WordPress SEO fine-tune by Meta SEO Pack from Poradnik Webmastera