Easy way to truncate multiple tables in SS 2000

Question

Easy way to truncate multiple tables in SS 2000

Khaos

SSC-Addicted

Points: 428
More actions
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.

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

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

Khaos SSC-Addicted Points: 428 More actions · Answer 2

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

mybid2003 SSCertifiable Points: 6620 More actions · Answer 5

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.

.

Khaos SSC-Addicted Points: 428 More actions · Answer 6

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

Greg Grow SSCrazy Points: 2386 More actions · Answer 7

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

Khaos SSC-Addicted Points: 428 More actions · Answer 8

thnx Prometheus, that sure did it!

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

Kris