SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_MSforeachtable Drop Tables


sp_MSforeachtable Drop Tables

Author
Message
mikenason
mikenason
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 47
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?
GTR
GTR
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1961 Visits: 368
In SQL 2008 SSMS Object explorer details tab sort the objects by row count.

EnjoY!
mikenason
mikenason
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 47
D__ that is Nice! It is still somewhat manuall but works great. Thanks
GTR
GTR
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1961 Visits: 368
Yes, you wanted to do it manually right?:-D

EnjoY!
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69055 Visits: 9671
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.
Susantha Bathige
Susantha Bathige
SSC Veteran
SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)SSC Veteran (278 reputation)

Group: General Forum Members
Points: 278 Visits: 282
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
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36408 Visits: 11361

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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search