dropping tables where rowcount is zero
-
Just wondering if anyone ever ran into a situation where they had a very large database (by sheer number of tables) and they wanted to drop all the empty tables?
I can find a number of scripts to do rowcounts, but I cant find any that drop tables where rowcount = 0.
Perhaps this old script on this site could be modified with an IF rowCnt = 0 but unsure if a cursor is the way to go.
Thoughts or ideas or modifications to this script are appreciated.
________________
SET NOCOUNT ON
DECLARE @tableName VARCHAR (255), @sql VARCHAR (300)
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName)
FETCH NEXT FROM myCursor INTO @tableName
END
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
CLOSE myCursor
DEALLOCATE myCursor
DROP TABLE #temp
-
Did you try the system table, sys.sysindexes?
-
You might also want to make sure there aren't any tables that are referenced by operational code, but which don't have rows at the moment.
For example, I've seen code that uses a "Flags" table. If some process is running, it puts a row in the Flags table. When it's done, it removes that row. When the process starts, it checks if the row already exists, and if it does, it either aborts or raises and error. Useful on certain long-running processes that you really don't want overlap on. (There are potentially better solutions, but this is a valid one.)
That table might not have any rows in it at any given time. But you still better not drop it.
I use "staging tables" for a number of bulk import processes. Data goes into them, gets a bunch of cleaning done on it, then the data is moved to permanent tables and the staging table is truncated. Kind of temp tables that get truncated instead of dropped. Again, those tables very often don't have anything in them, but if I dropped them, it would bring a whole department to a rather abrupt halt.
Just a word of warning. Be sure before you drop. Especially in a production database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
- be carefull with sysindexes, because it may have an estimate count.
- adapt your script:
e.g. like this
EXEC ('if 0 = (SELECT count(*) as rowCnt from ['+@schema+'].['+@tableName +']) drop table ['+@schema+'].['+@tableName +']' )Be carefull when doing this, because some tables may be empty temporarly or only in use during special runs, ...:ermm:
So be sure you have a full script of all definitions in your database !!!
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
You can get an accurate rowcount from sys.indexes if you first run DBCC UpdateUsage(0)
It'll be a lot faster than getting a row count from every table in a database with a "huge" number of tables.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
And probably you would be better off by first renaming your tobe obsolete tables, and removing them only a couple of months later
EXEC ('if 0 = (SELECT count(*) as rowCnt from ['+@schema+'].['+@tableName +']) EXECUTE sp_rename ''['+@schema+'].['+@tableName +']'', ''['+@schema+'].['+@tableName +'_Obsolete]'', 'OBJECT' ' )Be carefull if you have implemented DRI.
Use sp_keys / sp_fkkeys to inspect.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I took into consideration the update stats and renaming...thanks...and ended up with essentially the code below that seemed to work well enough though in the middle of the running the script I did find output errors twice...not sure what to make of it.
error~~~~~~~~~~~~~
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.
code used~~~~~~~~~~~~~~~
DBCC UpdateUsage(0)
go
SET NOCOUNT ON
DECLARE @tableName VARCHAR (255), @sql VARCHAR (300), @counter INT
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'base table'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('if 0 = (SELECT count(*) as rowCnt from ['+@tableName +']) EXECUTE sp_rename ''['+@tableName +']'', ''['+@tableName +'_Obsolete]''')
FETCH NEXT FROM myCursor INTO @tableName
END
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
CLOSE myCursor
DEALLOCATE myCursor
DROP TABLE #temp
-
You should probably exclude tables LIKE '%_Obsolete%'.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Run the same script again in one month.
If that results in '%_obsolete_obsolete' tables, that would mean it hasn't been used for a month, so maybe good candidate for cleanup.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ALZDBA (6/30/2008)
Run the same script again in one month.If that results in '%_obsolete_obsolete' tables, that would mean it hasn't been used for a month, so maybe good candidate for cleanup.
Actually, that's how I usually do it too.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply