Page 1 of 5 123 ... LastLast
Results 1 to 15 of 70
  1. #1
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Unanswered: How To Delete All Tables

    Hi All,

    How can i delete all the tables in a DATABASE with a single shot!!

    Thanx in advance

  2. #2
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29

    Re: How To Delete All Tables

    Well...what i do is may not be the right way)

    Just delete the database.......and create a new database........else it will start asking for so many dependencies)

    cheers!







    Originally posted by Saravanan.R
    Hi All,

    How can i delete all the tables in a DATABASE with a single shot!!

    Thanx in advance

  3. #3
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Benny,

    In a database TABLES, VIEWS, FUNCTIONS, PROCEDURES, TRIGGERS, etc. are there! So I have to delete only TABLES. If I drop the database means I want to recreated all those objects.

    I need to delete TABLES alone.

    Thanx in advance

  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    another simple way

    generate SQL script of the database, select "all tables", select "generate drop command for each object" check box only (not the create one). you should have a script with all drop commands. run it

  5. #5
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi

    Its not a appropriate way to drop! plz..

  6. #6
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    DELETE FROM sysobjects WHERE xtype='U'
    Need to check the option "update system catalogs" for the server properties prior to execute the statement.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Re: How To Delete All Tables

    Originally posted by Saravanan.R
    Hi All,

    How can i delete all the tables in a DATABASE with a single shot!!

    Thanx in advance
    DO NOT delete anything from sysobjects. That's insane.

    If you want to delete the tables (in SQL Server lingo, this just means delete the data out of all of them).

    DECLARE
    @sql VARCHAR(4000),
    @int_counter INT,
    @int_max INT

    DECLARE @tables TABLE(
    ident INT IDENTITY(1,1) PRIMARY KEY,
    table VARCHAR(256))

    INSERT @tables(table)
    SELECT name FROM sysobjects WHERE xtype = 'U'

    SELECT
    @int_counter = 1,
    @int_max = (SELECT MAX(ident) FROM @tables))

    WHILE @int_counter <= @int_max
    BEGIN

    SELECT @sql = 'DELETE ' + table
    FROM @tables WHERE ident = @int_counter

    SELECT @int_counter = @int_counter + 1
    END

    This will only work if you don't have foreign keys though, so you would have to make a similar procedure to drop and recreate those. You can find one on SQLServerCentral.com though.

    If you really want to just drop all the tables, change the DELETE to DROP TABLE, and you are good to go. NEVER edit the system tables as a shortcut. It's dangerous; and there are too many good scripts someone else has already written to be doing that.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Its not a appropriate way to drop!"???????

    What they heck WOULD be an appropriate way to drop all the tables in a database with dependent procedures, views, functions, and perhaps even triggers?

    I have trouble believing what you are doing is appropriate or necessary in the first place!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Originally posted by blindman
    "Its not a appropriate way to drop!"???????

    What they heck WOULD be an appropriate way to drop all the tables in a database with dependent procedures, views, functions, and perhaps even triggers?

    I have trouble believing what you are doing is appropriate or necessary in the first place!
    If he's dropping the tables to just recreate them, the biggest problem will be the foreign keys if he has them.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    He is just askin to delete ALL tables. Why r u scared of the foreign keys, obviously he would have the script for recreating the tables which would include the relationships too.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Again, What For???

    If he can run a script to restore them, presumably he could run upalsen's script solution to drop them...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    He says that he wanna delete all tables in one shot, just for fun i guess, therefore one delete query in sysobjects would satiate his desires rather to select all tables and then choosing include drop tables statement and then running the drop table commands for each table.
    However, it's clear that playin with system catalogs is not so wise. U r right indeed.

  13. #13
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Saravanan.R

    why do you want to do this
    it may be that we may have an alternate solution for your problem other than deleting all of your tables

    for example if you want to just remove all of the data from your tables without dropping them, then try the truncate table statement.

    [Books Online] Truncate Table

    ps if you ever directly modify a system table, we will run you out of town and burn your castle like a group of villagers chasing the frankestein monter

  14. #14
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Hi All,

    Sorry I have to DELETE (DROP ALL TABLES) in a single shot!

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You think he wants to drop all the tables in his database "just for fun"?

    What the heck are YOUR hobbies?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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