drop all tables in database

what is the sql query to drop all tables in a database in sql server 2000

Please excuse the fact that I have not tested this!

DECLARE

@.TABLEVARCHAR(100)
DECLARE @.EXECVARCHAR(100)
DECLARE XCURSORCURSORFORSELECTNAMEFROM SYSOBJECTSWHERE XTYPE='U'
OPEN XCURSOR
FETCH XCURSORINTO @.TABLE
WHILE@.@.FETCH_STATUS= 0
BEGIN
SET @.EXEC='DROP TABLE '+ @.TABLE
PRINT @.EXEC
EXEC(@.EXEC)
FETCH XCURSORINTO @.TABLE
END
CLOSE XCURSOR
DEALLOCATE XCURSOR|||

Only possible way of doing is by droping the entire database using

drop database "Database Name" command

|||>> Only possible way of doing is by droping the entire database using drop database "Database Name" command

The command I gave should work, but it might need to be repeated because of iintertable key relationships.

|||

I ran this one in TEMPDB and it works

DECLARE @.TABLE VARCHAR(100)
DECLARE @.LINK VARCHAR(100)
DECLARE @.EXEC VARCHAR(100)
DECLARE XCURSOR CURSOR FOR SELECT T.NAME TNAME, L.NAME AS LINK
FROM SYSOBJECTS T, SYSOBJECTS L, sysforeignkeys
WHERE L.XTYPE = 'F' AND fkeyid = T.id AND constid = L.id
OPEN XCURSOR
FETCH XCURSOR INTO @.TABLE, @.LINK
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.EXEC = 'ALTER TABLE [dbo].[' + @.TABLE + '] DROP CONSTRAINT ' + @.LINK
PRINT @.EXEC
EXEC (@.EXEC)
FETCH XCURSOR INTO @.TABLE, @.LINK
END
CLOSE XCURSOR
DEALLOCATE XCURSOR
DECLARE XCURSOR CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
OPEN XCURSOR
FETCH XCURSOR INTO @.TABLE
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.EXEC = 'DROP TABLE ' + @.TABLE
PRINT @.EXEC
EXEC (@.EXEC)
FETCH XCURSOR INTO @.TABLE
END
CLOSE XCURSOR
DEALLOCATE XCURSOR

|||

You can run this script in Query analyzer in (Resuls to Text mode), get the script and run it. I would advise caution though, make sure you are doing what you intend to. there's no way to rollback, except to restore from a previous copy of the Db.

select

'DROP TABLE '+ name+char(10)+char(13)+'Go'

FROM

sysobjectswhere type='u'