Dropping select tables across a database.

Question

Post reply

Dropping select tables across a database.

PearlJammer1

SSCrazy Eights

Points: 8480
More actions
May 28, 2013 at 8:30 am

#277993

Hi
I have a situation whereby I have hundreds of tables across my instances that need dropping. The tables are prefixed with a name of dataload.x_TableName_date
The date part is in the format of 20130508201455337 if this helps.
The situation has happened because whenever we need to do a datacleanse the Dev's take a backup of the table and then give it the new name, so i am left with hundreds of tables as mentioned above that are no longer needed.
Initially I only want to drop tables that are older than 1 month and then I might gradually decrease this amount.
Can anybody advise a method of doing this task - maybe via some kind of script as i dont fancy dropping each one manually !
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

You can use system tables like INFORMATION_SCHEMA.TABLES or sys.tables to create a DROP TABLE script.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's two examples of what Kingston was referring to;

I personally prefer to use the sql server specific metadata, rather than the information_schema, but it's effectively the same thing:

the only reason I prefer the SQL specific views is when you start digging for more specific information, (like which columns have identity property), The information_schema views don't have all the details needed to script out tables and columns completely.

SELECT 'DROP TABLE ' + QUOTENAME(name) + ';' AS cmd

FROM sys.tables

WHERE name like '%20130508201455337%'

SELECT 'DROP TABLE ' + QUOTENAME(TABLE_NAME) + ';' AS cmd

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME like '%20130508201455337%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PearlJammer1 SSCrazy Eights Points: 8480 More actions · Answer 3

Thanks for your reply.

Can you expand upon your answer please ? Or could you point me to where i may be able to see some code that does something similar ?

Thanks

PearlJammer1 SSCrazy Eights Points: 8480 More actions · Answer 4

PearlJammer1

SSCrazy Eights

Points: 8480

May 28, 2013 at 8:47 am

#1618985

Thanks Lowell.

I'll look into that technique:-)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

Lowell (5/28/2013)
The information_schema views don't have all the details needed to script out tables and columns completely.

I agree. But sometimes(when some specific information is not needed, like in this case) I prefer using the INFORMATION_SCHEMA tables as the names of the columns are very meaningful and easy to understand. 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

PearlJammer1 (5/28/2013)
The situation has happened because whenever we need to do a datacleanse the Dev's take a backup of the table and ...

As a bit of a sidebar, there's no way that I'd allow developers to have the privs to do such a thing (data cleanup) in a production database. It's an accident waiting to happen. Better check and make sure that your backups can actually be restored on a regular basis if you're going to continue to allow it.

A better alternative would be to either have them submit a script to do it that will be reviewed and executed by the DBA or to build a well tested and Gumby-proof stored procedure to do the cleanup within a well thought out transaction. The proc could also be setup to make and delete older safety copies of the table. There's no need for Developers to have any modification privs in production.

--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)

OTF SSCertifiable Points: 6104 More actions · Answer 7

PearlJammer1 (5/28/2013)
The situation has happened because whenever we need to do a datacleanse the Dev's take a backup of the table and then give it the new name, so i am left with hundreds of tables as mentioned above that are no longer needed.

I can only imagine the clutter this might create in your database.

If you are on a Sql Server Edition that supports Database Snapshots you can probably do one of those prior to any changes then manage dropping of the Snapshots as you see fit.

Otherwise, you can maybe have the backups done to a Temp. database somewhere... less risky and can be managed independently of your primary database.

PearlJammer1 SSCrazy Eights Points: 8480 More actions · Answer 8

Thanks everybody for all the tips.

I finally came up with the following approach using a cursor to loop through the database. I first checked the tables the code returned using the print statement (the drop command is commented out in this example).

The tables i wanted to drop all began with z_ and i was sure i had no tables begining with z_ that where being used in production !! I used the where command to filter on the begining of the table name, the object and the date it was created. It worked for me - there is probably an easier way - if there is please post away !!!:-)

SELECT name AS Object_Name

,type_desc as UserTable

,create_date AS CreateDate

FROM sys.objects

WHERE name like 'z_%' AND type_desc like 'user_table' and create_date < '10-may-2013'

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @tname, @typedesc, @createdate

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'Drop Table ' + @tname +' ' + @typedesc +' ' + @createdate

--EXEC (@sql)

print (@sql)

FETCH NEXT FROM db_cursor INTO @tname, @typedesc, @createdate

END

CLOSE db_cursor

DEALLOCATE db_cursor