Finding Empty Tables in a database

Databases have the unfortunate habit of ending up with a slew of unused tables and stored procedures. The user requirements change, and somehow the 'clean up the database' task seems to have no priority. Empty tables are not neccesarily tables that are not being used - but atleast they are a start when you begin the task of cleaning up the database.

create procedure findEmptytables

as
-- create table and do not populate it
create table EmptyTable (c1 int)

-- create a cursor to go through each table in a database
-- and print the name of any empty tables
DECLARE @SQLString nvarchar (255),
                    @ParmDefinition nvarchar (255)

DECLARE @tablename sysname, @Empty char (1)

DECLARE FindEmptyTables CURSOR READ_ONLY
FOR SELECT TABLE_NAME
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_TYPE = 'BASE TABLE'

OPEN FindEmptyTables

FETCH NEXT FROM FindEmptyTables INTO @tablename
WHILE (@@fetch_status = 0)
  BEGIN
    SET @SQLString = N'IF EXISTS (SELECT * FROM "' + @tablename + '") set
@Empty = ''N'' ELSE set @Empty = ''Y'''
    SET @ParmDefinition = N'@tablename sysname, @Empty char(1) OUTPUT'

    EXECUTE sp_executesql
        @SQLString,
        @ParmDefinition,
        @tablename = @tablename,
        @Empty = @Empty OUTPUT

    IF @Empty = 'Y' and @tablename != 'EmptyTable' PRINT @tablename + ' is empty'
    FETCH NEXT FROM FindEmptyTables INTO @tablename
  END

CLOSE FindEmptyTables
DEALLOCATE FindEmptyTables
drop table EmptyTable
GO

by Llama Lopon