October 5, 2011 at 3:26 pm
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?
October 5, 2011 at 6:39 pm
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?
October 6, 2011 at 5:23 am
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
October 6, 2011 at 10:28 am
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.
October 12, 2011 at 8:39 am
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.
October 12, 2011 at 1:25 pm
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.”
October 12, 2011 at 11:58 pm
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...
October 13, 2011 at 12:05 am
derekr 43208 (10/12/2011)
That should do itRemember 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.”
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply