Saturday, February 9, 2008

Dropping all tables in the current database

WHILE EXISTS(SELECT [name] FROM sys.tables WHERE [type] = 'U')
BEGIN
DECLARE @table_name varchar(50)
DECLARE table_cursor CURSOR FOR SELECT [name] FROM sys.tables WHERE [type] = 'U'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
EXEC ('DROP TABLE [' + @table_name + ']')
PRINT 'Dropped Table ' + @table_name
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM table_cursor INTO @table_name
END
CLOSE table_cursor
DEALLOCATE table_cursor
END