Post reply

Easy way to truncate multiple tables in SS 2000

  • September 10, 2003 at 7:56 am

    #81970

    hi all,

    Whenever I want to truncate a table...I have to type someting like 'truncate table dbo.adress' in a sql window. Is there an easier way to truncate one or multiple tables (so you dont have to make a sql-script for every table you want to truncate).

    I'm looking for a method like the one which is provided in Quest Software's TOAD (for oracle). With the use of a button I can truncate or drop a table.

    Is this possible in SQL Server 2000 or with a third-party tool?

    thnx,

    Kristiaan Soomers

    The Netherlands.

  • September 10, 2003 at 8:32 am

    #473437

    What about to use select 'truncate table ' + name from dbo.sysobjects where xtype = 'U' to generate truncate statements for all your user tables.

  • September 10, 2003 at 8:42 am

    #473442

    sure...that will do the trick. But what if I only want to truncate one or two tables?

    I would just love it, if the command would be under a button (just like TOAD).

    If that isn't possible, I won't have to look any further 😉

    Kris

  • September 10, 2003 at 8:47 am

    #473444

    Hi Kris,

    quote:

    sure...that will do the trick. But what if I only want to truncate one or two tables?

    I would just love it, if the command would be under a button (just like TOAD).

    If that isn't possible, I won't have to look any further 😉

    you're looking for something that's nicely wrapped in a GUI?

    AFAIK, no chance in EM or QA for TRUNCATE.

    DROP works when you right-click on the table in question and choose Delete. But you have to go through every single table ou want to do this for.

    ...and personally I think this is good so.

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 10, 2003 at 8:53 am

    #473445

    Well, after a little bit longer.

    Hm, when the table(s) you want to do this for hardly never change, why not use Allen suggestion, create a stored procedure, and customize in QA a shortcut to fire your sp?

    Just another thought.

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • September 10, 2003 at 9:24 am

    #473457

    I normally write in SQL and execute in QA and save the script based on the date. This helps me to look back what happend earlier kinda scenarios. But if I am in hurry to get the things done, I do Right Click on the table in Embarcadero's DBArtisan and select Truncate. I can even select multiple tables.

    But just for this feature DBArtisan will not be an option.

    .

  • September 11, 2003 at 12:33 am

    #473611

    Thnx Mdamera,

    I've downloaded an evaluation copy op DBartisan and it really comes through! Nice tool, good advice.

    All the other tips were also very helpfull, thnx a lot guys!

    Kris

  • September 11, 2003 at 10:40 am

    #473760

    What I have done was created a simple stored procedure that accepts the parameter of @tablename.

    CREATE PROCEDURE sps_TruncateTable (@Table VARCHAR(100))

    AS

    BEGIN

    EXEC ('TRUNCATE TABLE ' + @Table)

    END

    Then add the stored procedure to your custom hot keys in query analyzer. All you need to do is type the table or highlight the table from another query and hit the hotkey. That is about as close as I can get.

    Greg

  • September 12, 2003 at 12:30 am

    #473867

    thnx Prometheus, that sure did it!

    Now if only I could add that same hotkey to the Enterprise manager 😉

    Kris

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

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