Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_MSforeachtable Drop Tables Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 9:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 31, 2011 9:25 AM
Points: 4, 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?
Post #881138
Posted Thursday, March 11, 2010 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 20, 2013 9:25 AM
Points: 389, Visits: 357
In SQL 2008 SSMS Object explorer details tab sort the objects by row count.

EnjoY!
Post #881149
Posted Thursday, March 11, 2010 10:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 31, 2011 9:25 AM
Points: 4, Visits: 47
D__ that is Nice! It is still somewhat manuall but works great. Thanks
Post #881165
Posted Thursday, March 11, 2010 10:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 20, 2013 9:25 AM
Points: 389, Visits: 357
Yes, you wanted to do it manually right?

EnjoY!
Post #881175
Posted Thursday, March 11, 2010 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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.
Post #881181
Posted Friday, March 12, 2010 1:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 9:48 AM
Points: 52, Visits: 275
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
Post #881554
Posted Friday, March 12, 2010 4:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 11,192, Visits: 11,090
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #881618
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse