Originally posted by Saravanan.R
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 @tables TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
SELECT name FROM sysobjects WHERE xtype = 'U'
@int_counter = 1,
@int_max = (SELECT MAX(ident) FROM @tables))
WHILE @int_counter <= @int_max
SELECT @sql = 'DELETE ' + table
FROM @tables WHERE ident = @int_counter
SELECT @int_counter = @int_counter + 1
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.