sp_MSforeachtable Drop Tables

  • I am attempting to drop/delete all tables in a database that have no data, i.e. rowcount(0) is zero. I have 10 DB's to clean up and each has over a 1000 tables so I am not wanting to do this manually. Ideas/suggestions on how to use sp_MSforeachtable to accomplish this?

  • In SQL 2008 SSMS Object explorer details tab sort the objects by row count.

    EnjoY!
  • D__ that is Nice! It is still somewhat manuall but works great. Thanks

  • Yes, you wanted to do it manually right?:-D

    EnjoY!
  • Most reporting table are empty untill they are used....

    Might want to script them and move them to a backup db in case you need them later.

  • Here is another method;

    Create a stored proc in each DB to delete the empty tables and then exec that SP by using sp_MsForEachDB.

    Susantha

  • USE tempdb;

    -- Will hold a list of empty tables in all user databases

    DECLARE @EmptyTables

    TABLE (table_name NVARCHAR(MAX));

    -- Cursor

    DECLARE @databases CURSOR;

    -- Used with the cursor

    DECLARE @DBName SYSNAME;

    -- Declare the cursor

    SET @databases =

    CURSOR

    LOCAL

    FORWARD_ONLY

    STATIC

    READ_ONLY

    TYPE_WARNING

    FOR

    SELECT name = QUOTENAME(D.name)

    FROM sys.databases D

    WHERE D.database_id > 4 -- not master, model, tempdb, msdb

    AND D.name NOT LIKE 'ReportServer$%' -- not Report Server

    AND D.source_database_id IS NULL -- not a snapshot

    AND D.state_desc = N'ONLINE' -- is online

    AND D.user_access_desc = N'MULTI_USER' -- open for all users

    AND D.is_read_only = 0 -- not read-only

    AND D.is_distributor = 0; -- not a distribution database

    -- Open the cursor

    OPEN @databases;

    -- Cursor loop

    WHILE (1 = 1)

    BEGIN

    -- Next database name

    FETCH @databases INTO @DBName;

    -- Row missing...continue with next database

    IF @@FETCH_STATUS = -2 CONTINUE;

    -- No more rows...exit

    IF @@FETCH_STATUS = -1 BREAK;

    -- Add the empty tables in the current database

    -- The dynamic SQL finds all tables that consist of

    -- a single partition with no rows. Only user

    -- tables that are not marked as MSFT entities are

    -- considered. A special exception is made for

    -- sysdiagrams (used for SSMS database diagrams)

    INSERT @EmptyTables

    (table_name)

    EXECUTE (

    'USE ' + @DBName + ';' +

    '

    SELECT table_name =

    QUOTENAME(DB_NAME())

    + NCHAR(46) +

    QUOTENAME(OBJECT_SCHEMA_NAME(P.[object_id]))

    + NCHAR(46) +

    QUOTENAME(OBJECT_NAME(P.[object_id]))

    FROM sys.partitions P

    JOIN sys.objects O

    ON O.[object_id] = P.[object_id]

    WHERE O.type_desc = N''USER_TABLE''

    AND O.is_ms_shipped = 0

    AND O.name NOT IN (N''sysdiagrams'')

    GROUP BY

    P.[object_id]

    HAVING SUM(P.rows) = 0

    AND MAX(P.partition_number) = 1;

    ');

    END;

    -- Show the DROP TABLE statements

    SELECT N'DROP TABLE ' + ET.table_name + N';'

    FROM @EmptyTables ET;

    -- Clean up the cursor

    IF CURSOR_STATUS(N'variable', N'@Databases') > 0

    BEGIN

    CLOSE @databases;

    DEALLOCATE @databases;

    END;

    GO

    -- END SCRIPT

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply