Drop table

  •  

    i need to delete  specific tables "_backup " which took before changes to the table, instead  of deleting the table one by one , any script is there to complete the task at ones.

  • You have to query the system tables for the list of tables matching your criteria, and then use a cursor to delete them (because you have to do it one at a time).

    This is crazy ugly, but it works... maybe it will give you some ideas.

    DECLARE @objName SYSNAME;

    DECLARE @cursor_Tables AS CURSOR;

    SET @cursor_Tables = CURSOR FOR
    SELECT ao.[name] as objName
    FROM sys.all_objects ao
    WHERE ao.name LIKE '%_backup'
    AND type = 'U';

    OPEN @cursor_Tables;
    FETCH NEXT FROM @cursor_Tables INTO @objName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET NOCOUNT ON;
    PRINT @objName;
    --BACKUP DATABASE @name TO DISK = @fileName
    DECLARE @sql NVARCHAR(MAX)

    SET @sql = 'DROP TABLE dbo.' + QUOTENAME(@objName);
    print @sql;
    -- EXEC sp_executesql @sql;

    FETCH NEXT FROM @cursor_Tables INTO @objName;

    END
    close @cursor_Tables;
    deallocate @cursor_Tables;
  • Is  the query will provide  the drop syntax of all the tables _backup or it will delete the tables on a database .

  • The query already drops tables in the query at the top.

    It fetches each table name 1 by 1 out of the query and executes a drop table + table name

    SELECT ao.[name] as objName
    FROM sys.all_objects ao
    WHERE ao.name LIKE '%_backup'
    AND type = 'U';

    I want to be the very best
    Like no one ever was

  • ramyours2003 wrote:

    i need to delete  specific tables "_backup " which took before changes to the table, instead  of deleting the table one by one , any script is there to complete the task at ones.

    Step 1: If it's a one time thing (i.e. not needed as part of a stored procedure and is rather ad hoc in nature), open SSMS, press the [F8] key to open the Object Explorer window (if it's not already open), expand the database in question, right click on tables, select [FILTER SETTINGS], enter the criteria for the table name, and press [OK].

    Step 2: Click on tables in the Object Explorer again and then press the [F7] key.  The Object Explorer Details window will open with only all of the filtered table names.  Review them to make sure that you're not getting to drop something unexpected.  Highlight all of the table names and then do a [CTRL] click to deselect any of the filtered tables in the list you may want to keep.  Again, review your selections to be sure.

    Step 3:  Press the [DELETE] key and confirmation window will appear.  Last time to check and make sure.  If you're positive as to the tables listed to be deleted, click [OK] and all of the selected tables will be deleted.  If the system is not able to delete a table, it will be listed as an error.

    Strep 4:  Please don't take this as being a smarty pants or a blast on you.  While I agree that you should learn how to do a lot of things in code, SSMS can do many amazing things with just several clicks that can make such ad hoc tasks quite simple.  I strongly urge folks to read the documentation about SSMS and seek out presentations and articles on the subject of SSMS and tricks in SSMS and useful things in SSMS because it can make your life pretty easy.  In a lot of cases, it'll also generate a script of changes that you can copy and paste into SSMS for further study provided that you understand that it frequently adds some totally unnecessary stuff to the script (like 3 or 4 USE statements that don't seem to make any sense at all).  It will also sometimes generate way too much like if you use it to do some simple table partitioning.  If you're not aware, it frequently generates an additional command to undo all the partitioning it just generated code for and will sometimes even have code to drop the table that the other code just created.  ALWAYS CAREFULLY STUDY SUCH GENERATED CODE FOR SUCH NUANCES.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

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