Deleting multiple tables with information schema

  • 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?

  • 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?

  • 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

  • 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.

  • 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.

  • 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.”

  • 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...

  • 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.”

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

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