Deleting multiple tables with information schema

Question

Deleting multiple tables with information schema

SQLTestUser

Hall of Fame

Points: 3997
More actions
October 5, 2011 at 3:26 pm

#390552

is there a way to delete multiple tables with information schema
i tried
Delete from information_schema.tables where table_name like '%_XX%'
and got an error as
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
is there another way to do a delete of such sort?

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

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

mendesm Ten Centuries Points: 1074 More actions · Answer 1

There's a difference between dropping a table (removing them completely from the database) and deleting their contents. Which one are you trying to do?

SQLSACT SSC-Insane Points: 21838 More actions · Answer 2

You could write a query that goes through Information_schema and creates a drop table script

Something like:

set nocount on

select 'DROP TABLE '+table_schema+'.'+table_name+'

GO'

from INFORMATION_SCHEMA.TABLES where TABLE_NAME = ''

This is assuming you have no constraints etc on the tables in question

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 3

thanks for the script, I did get the answer i don't believe there is any way we can delete multiple tables( the whole table with the contents in it, not the data and the definition) with a single script, i would have to generate a script as you have guided me.

thanks again.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

SQLTestUser (10/6/2011)
thanks for the script, I did get the answer i don't believe there is any way we can delete multiple tables( the whole table with the contents in it, not the data and the definition) with a single script, i would have to generate a script as you have guided me.
thanks again.

You could probably get that right by using Dynamic SQL.

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 5

DECLARE @TABLENAME VARCHAR (10)

DECLARE DROPTABLE CURSOR

FOR SELECT NAME FROM INFORMATION_SCHEMA.TABLES -- OR SYSTABLES

WHERE NAME LIKE '%SHI%'

OPEN DROP TABLE

FETCH NEXT FROM DROPTABLE

INTO @TABLENAME

WHILE (@@FETCH_STATUS = 0)

BEGIN

DECLARE @SQL VARCHAR (500)

SET @SQL = 'DROP TABLE ' + @TABLENAME

DBNAME.SP_EXECUTESQL @SQL

FETCH NEXT FROM DROPTABLE

INTO @TABLENAME

END

DEALLOCATE DROPTABLE

CLOSE DROPTABLE

This might help 🙂

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

That should do it

Remember though, this is predicated on the fact that you have no constraints on the tables you want to delete. Foreign Keys etc...

SQL_By_Chance Hall of Fame Points: 3915 More actions · Answer 7

derekr 43208 (10/12/2011)
That should do it
Remember though, this is predicated on the fact that you have no constraints on the tables you want to delete. Foreign Keys etc...

Exactly , in case you have foreign keys than you can refer to Foreignkeys table to create a similar cursor and drop them first

______________________________________________________________________

Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”