Professional Web Applications Themes

drop all user tables in a user database? - Microsoft SQL / MS SQL Server

Hi, I need some help in sql server. I need a procedure that will drop all user tables in a user database(let's pubs). Can anyone help me? Thank you very much in advance. Deda Zheng...

  1. #1

    Default drop all user tables in a user database?

    Hi,

    I need some help in sql server.
    I need a procedure that will drop all user tables in a
    user database(let's pubs).
    Can anyone help me?
    Thank you very much in advance.

    Deda Zheng

    dzheng Guest

  2. #2

    Default Re: drop all user tables in a user database?

    Hi,

    I am a new guy in sql server. I need a complete procedure
    that I can use to run the drop all user tables.
    Thanks.

    Deda Zheng
     
    disappear or work 
    INFORMATION_SCHEMA.TABLES to get a 
    ('DROP TABLE '+tblName) 
    >
    >
    >.
    >[/ref]
    dzheng Guest

  3. #3

    Default Re: drop all user tables in a user database?

    this might have troubles with those pesky FKs

    cheers,
    </wqw>

    "Aaron Bertrand - MVP" <com> wrote in message
    news:#phx.gbl... 
    '+tblName) 
    >
    >[/ref]


    Vlad Guest

  4. #4

    Default Re: drop all user tables in a user database?

    > I am a new guy in sql server. I need a complete procedure 

    Why does this need to be in a procedure? Are you going to be doing this
    often?

    Did you look in Books Online for the CREATE PROCEDURE topic?


    CREATE PROCEDURE dbo.dropAllTables
    AS
    BEGIN
    EXEC sp_msForEachTable 'DROP TABLE ?'
    END
    GO

    (Note that you might have to disable constraints/FKs first.)


    Aaron Guest

  5. #5

    Default Re: drop all user tables in a user database?

    Hi Aaron,

    EXEC sp_msForEachTable 'DROP TABLE ?' won't work when you have referential
    integrity in your database, as it won't drop the tables in the right order,
    so the easiest is to drop the foreign keys first:

    DECLARE sql VARCHAR(4000)
    WHILE 1=1
    BEGIN
    SET sql = (SELECT TOP 1 'ALTER TABLE ' + table_name + ' DROP CONSTRAINT
    ' + constraint_name
    FROM information_schema.table_constraints
    WHERE constraint_type = 'Foreign Key'
    IF sql IS NULL BREAK
    EXEC (sql)
    END

    and then drop the tables:

    WHILE 1=1
    BEGIN
    SET sql = (SELECT TOP 1 'DROP TABLE ' + table_name
    FROM information_schema.tables
    WHERE table_type = 'Base Table'
    IF sql IS NULL BREAK
    EXEC (sql)
    END

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Aaron Bertrand - MVP" <com> wrote in message
    news:#phx.gbl... 
    '+tblName) 
    >
    >[/ref]


    Jacco Guest

  6. #6

    Default Re: drop all user tables in a user database?

    > this might have troubles with those pesky FKs

    Well, you could do this first to clear all FKs:


    DECLARE cmd VARCHAR(255)
    DECLARE foo CURSOR
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT 'ALTER TABLE '+so1.name
    + ' DROP CONSTRAINT '+so2.name
    FROM sysobjects so1, sysobjects so2
    WHERE so1.id = so2.parent_obj
    AND so2.xtype = 'F'
    OPEN foo
    FETCH NEXT FROM foo INTO cmd
    WHILE FETCH_STATUS = 0
    BEGIN
    EXEC(cmd)
    FETCH NEXT FROM foo INTO cmd
    END
    CLOSE foo
    DEALLOCATE foo


    Aaron Guest

  7. #7

    Default Re: drop all user tables in a user database?

    > EXEC sp_msForEachTable 'DROP TABLE ?' won't work when you have referential 

    Yes, I made the assumption that someone who was dropping ALL tables often
    enough to require a procedure to do so, wasn't bothering with keys. I
    corrected that, but you were a minute ahead of me it seems. :-) I used
    sysobjects but I like the use of i_s.table_constraints better.

    A


    Aaron Guest

Similar Threads

  1. Select from one of three tables based on user input
    By Alan in forum Microsoft Access
    Replies: 2
    Last Post: September 8th, 04:46 AM
  2. looping through user tables to add a field ?
    By Dinesh.T.K in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 1st, 08:55 PM
  3. Replies: 2
    Last Post: July 14th, 05:30 PM
  4. Replies: 4
    Last Post: July 1st, 06:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139