Drop all tables created after a certain date

[foz]

Is it possible to delete all tables that were created after a certain date.

 

I can get all the table names where the created date is after a certain date. So then i tried to do

delete from sysobjects where crdate > 'somedate'

I get an error saying adhoc updates to system catalogs are not enabled. I'm sure this is for a reason.

 

Is there a way to drop a few tables at once. Something like drop table tablename where table name like '%sometext%', of course I just use this as an example.

 

cheers

 

foz

[PlausiblyDamp]

Not sure if it's the only way (or even a good way) but you could do this with a cursor

DECLARE tables_cursor CURSOR FOR
SELECT name from sysobjects
WHERE crdate >= '' and type = 'U'

declare @tablename sysname
OPEN tables_cursor

-- Perform the first fetch.
FETCH NEXT FROM tables_cursor
into @TableName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('drop table ' + @tablename)
  -- This is executed as long as the previous fetch succeeds.
  FETCH NEXT FROM tables_cursor
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

 

usual disclaimers about running things like this on your own servers apply i.e. have a backup, test on a non-critical system etc ;)

[foz]

are cursors like looping controls for sql?

[PlausiblyDamp]

Normally databases like SQL like to work on data as a set - one operation that can affect many rows at once (SELECT, DELETE, UPDATE) and as long as the where clause is well written then this is the most effective way to cause mass updates.

Cursors allow you to loop through records that match a criteria one at a time - effectively allowing you to loop through records one by one.

[foz]

cheers plausibly, code works great. I just had to change where you have

 

FETCH NEXT FROM tables_cursor in the while loop

to

FETCH NEXT FROM tables_cursor into @tablename

 

I've tested it just printing out the variable @tablename with the code:

exec ('print '''+@tablename+'''')

and everything is working great.

[PlausiblyDamp]

Whoops - my bad. Can't even cut and paste properly :)