jamieleshaw

How Can You Delete All Database Table Items At Once.


Re: Windows Forms Data Controls and Databinding Delete

Rong-Chun Zhang - MSFT

Hi jamieleshaw,

Based on your post, you want delete all tables of a database, right If so, these is no simple SQL statement which allow you delete all tables, however, you use the following statement to get all the tables name.

Code Snippet
select [name] from sysobjects where [type] = 'u'

Or

Code Snippet
sp_tables NULL,NULL,NULL,"'TABLE'"

Then use the DROP TABLE [table name] to delete table one by one. I take SQL Server for example, and for other database system you can take the logic.

Best regards.






Re: Windows Forms Data Controls and Databinding Delete

hrubesh

havent tested if this works .. try it cautiously please. ( test whether select [name] from sysobjects where [type] = 'u' returns the right table names first)

also it depends if you have relations... this wont work definitely.

sql

Code Snippet

DECLARE @stmt nvarchar(max)

DECLARE @aTable nvarchar(max)

DECLARE TablesToDeleteCursor Cursor FOR select [name] from sysobjects where [type] = 'u'

Open TablesToDeleteCursor

Fetch NEXT FROM TablesToDeleteCursor INTO @aTable
While @@FETCH_STATUS = 0
BEGIN

SET @stmt = 'DROP TABLE '+ @aTable
exec(@stmt)

Fetch NEXT FROM TablesToDeleteCursor INTO @aTable
END
CLOSE TablesToDeleteCursor
DEALLOCATE TablesToDeleteCursor







Re: Windows Forms Data Controls and Databinding Delete

Rong-Chun Zhang - MSFT

Hi hrubesh,

Thank you. You are right. When working with data relations, this method wonĄŻt work. But we can get all the Foreign Keys using the following statement

Code Snippet
select a.name as constraint_name, a.parent_obj ,b.name as parent_table_name from sysobjects a join sysobjects b on a.parent_obj = b.id where a.type = 'F'

Delete the foreign key constraint first.

Code Snippet
alter table [table_name] drop constraint [contraint_name]

Then delete all tables.

Best regards.






Re: Windows Forms Data Controls and Databinding Delete

hrubesh

Nice Rong-Chun Zhang,

So here is the code.

BEGIN TRAN SafeLyPreventDeletion -- To Remove when really deleting the tables

DECLARE @stmt nvarchar(max)

DECLARE @aTable nvarchar(max)

DECLARE @aRelation nvarchar(max)

DECLARE TablesToDeleteCursor Cursor FOR select [name] from sysobjects where [type] = 'u'

-- A cursor to delete relations first.

DECLARE aTableRelations CURSOR FOR SELECT B.name,A.NAME from sysobjects a join sysobjects b

on a.parent_obj = b.id where a.type = 'F'

OPEN aTableRelations

FETCH NEXT FROM aTableRelations INTO @aTable,@aRelation

While @@FETCH_STATUS = 0

BEGIN

SELECT 'DROPPING RELATION ' + @aRelation

SET @stmt = 'alter table '+ @aTable + ' drop constraint ' + @aRelation

EXEC(@stmt)

FETCH NEXT FROM aTableRelations INTO @aTable,@aRelation

END

CLOSE aTableRelations

DEALLOCATE aTableRelations

-- Now that relations are dropped, drop tables too.

OPEN TablesToDeleteCursor

FETCH NEXT FROM TablesToDeleteCursor INTO @aTable

While @@FETCH_STATUS = 0

BEGIN

SELECT 'DROPPING TABLE ' + @aTable

SET @stmt = 'DROP TABLE '+ @aTable

EXEC(@stmt)

Fetch NEXT FROM TablesToDeleteCursor INTO @aTable

END

CLOSE TablesToDeleteCursor

DEALLOCATE TablesToDeleteCursor

ROLLBACK TRAN SafeLyPreventDeletion -- To Remove when really deleting the tables