March 11, 2010 at 9:43 am
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?
March 11, 2010 at 9:50 am
In SQL 2008 SSMS Object explorer details tab sort the objects by row count.
March 11, 2010 at 10:03 am
D__ that is Nice! It is still somewhat manuall but works great. Thanks
March 11, 2010 at 10:26 am
Yes, you wanted to do it manually right?:-D
March 11, 2010 at 10:37 am
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.
March 12, 2010 at 1:14 am
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
March 12, 2010 at 4:03 am
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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply