Delete Data in All Tables

Guest · Dec 6, 2006

I have an SQL Server Database with about 125 tables. Each table has an
Identity Column and there are many Relationships setup. I need to create a
stored procedure thta will step through each table and delete the data from
each table and reseed the Identity Column to 1.

I'd like my stored procedure to just step through each table without regards
to the order of the table. This is because I want the procedure to continue
to work after I add additional tables.

Is there a streamlined process that will delete the data from every table
and reseed every Identity Column back to 1?

Any help with a solution for this would be greatly appreciated.

FYI: I have created a script that will rebuild my database from scatch. It
appears to build the database fine, but once I start testing my application
new errors appear, that don't appear in my test database. Plus, for some
reason, some tables just can't be accessed from Microsoft Access. So, using
the script like ths for now is not an option.

In summary, I need to be able to delete all test data from my database so
that I can import new data and send to a customer.

Any help will be greatly appreciated.

Greg

Charles Wang[MSFT] · Dec 7, 2006

Hi Greg,
From your description, I understand that:
You want to write a stored procedure which can help you clear all the data
of your database tables. You need not pay attention to the constraints and
order of the tables. After clear the data, you want to reseed the identity
column to 1.
If I have misunderstood, please let me know.

I am afraid that you need to use dynamic T-SQL for this issue.
I write a simple stored procedure for your reference:
ALTER PROCEDURE [dbo].[proc_cleardata]
AS
DECLARE @strTableName varchar(50)
DECLARE @strALTSQL varchar(8000)
DECLARE @strDELSQL varchar(8000)
DECLARE @strENBSQL varchar(8000)
DECLARE @strReseedSQL varchar(8000)
SET @strALTSQL = ''
SET @strDELSQL = ''
SET @strENBSQL = ''
SET @strReseedSQL =''
DECLARE cur_table_name CURSOR FOR
SELECT name from sysobjects where xtype='U'

OPEN cur_table_name
FETCH NEXT FROM cur_table_name INTO @strTableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strALTSQL = @strALTSQL + 'ALTER TABLE ' + @strTableName + ' NOCHECK
CONSTRAINT ALL; '
SET @strENBSQL = @strENBSQL + 'ALTER TABLE ' + @strTableName + ' CHECK
CONSTRAINT ALL; '
SET @strDELSQL = @strDELSQL + 'DELETE FROM '+ @strTableName+'; '
SET @strReseedSQL = @strReseedSQL + 'DBCC
CHECKIDENT('''+@strTableName+''',RESEED,0); '
FETCH NEXT FROM cur_table_name INTO @strTableName
END
CLOSE cur_table_name
DEALLOCATE cur_table_name

--PRINT @strALTSQL
--PRINT @strENBSQL
--PRINT @strDELSQL
--PRINT @strReseedSQL

--Disable checking constraints
EXEC (@strALTSQL)
--Delete data from tables
EXEC (@strDELSQL)
--Reseed to 1
EXEC (@strReseedSQL)
--Enable checking constraints
EXEC (@strENBSQL)

Hope this helps!
Please feel free to let me know if you need further assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

David Portas · Dec 8, 2006

Charles Wang said:
Hi Greg,
From your description, I understand that:
You want to write a stored procedure which can help you clear all the data
of your database tables. You need not pay attention to the constraints and
order of the tables. After clear the data, you want to reseed the identity
column to 1.
If I have misunderstood, please let me know.

I am afraid that you need to use dynamic T-SQL for this issue.
I write a simple stored procedure for your reference:
ALTER PROCEDURE [dbo].[proc_cleardata]
AS
DECLARE @strTableName varchar(50)
DECLARE @strALTSQL varchar(8000)
DECLARE @strDELSQL varchar(8000)
DECLARE @strENBSQL varchar(8000)
DECLARE @strReseedSQL varchar(8000)
SET @strALTSQL = ''
SET @strDELSQL = ''
SET @strENBSQL = ''
SET @strReseedSQL =''
DECLARE cur_table_name CURSOR FOR
SELECT name from sysobjects where xtype='U'

OPEN cur_table_name
FETCH NEXT FROM cur_table_name INTO @strTableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strALTSQL = @strALTSQL + 'ALTER TABLE ' + @strTableName + ' NOCHECK
CONSTRAINT ALL; '
SET @strENBSQL = @strENBSQL + 'ALTER TABLE ' + @strTableName + ' CHECK
CONSTRAINT ALL; '
SET @strDELSQL = @strDELSQL + 'DELETE FROM '+ @strTableName+'; '
SET @strReseedSQL = @strReseedSQL + 'DBCC
CHECKIDENT('''+@strTableName+''',RESEED,0); '
FETCH NEXT FROM cur_table_name INTO @strTableName
END
CLOSE cur_table_name
DEALLOCATE cur_table_name

--PRINT @strALTSQL
--PRINT @strENBSQL
--PRINT @strDELSQL
--PRINT @strReseedSQL

--Disable checking constraints
EXEC (@strALTSQL)
--Delete data from tables
EXEC (@strDELSQL)
--Reseed to 1
EXEC (@strReseedSQL)
--Enable checking constraints
EXEC (@strENBSQL)

Hope this helps!
Please feel free to let me know if you need further assistance.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
======================================================

I would recommend one significant change to this. Use the WITH CHECK option
when enabling the constraints. Otherwise the constraints will be untrusted
and the optimizer may not take advantage of them.

SET @strENBSQL = @strENBSQL + 'ALTER TABLE '
+ @strTableName + ' WITH CHECK CHECK CONSTRAINT ALL; ';

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Charles Wang[MSFT] · Dec 13, 2006

Hi Greg,
How about this issue?

Please feel free to let us know if you have any other questions or concerns.

Sincerely yours,
Charles Wang
Microsoft Online Community Support

aaron.kempf · Dec 14, 2006

I think that you'd be better off with truncate in most situations..
delete from SUCKS in my book.. as an alternative you can do something
simpler like this:

Select 1 As SortOrder, 'TRUNCATE TABLE [' + Name + ']' as
Paste_Me_In_Query_Analyzer

From sysobjects

Where Xtype = 'U'
UNION ALL
Select 2 As SortOrder, ' DELETE FROM [' + Name + ']' as
Paste_Me_In_Query_Analyzer

From sysobjects

Where Xtype = 'U'

then you just take the results (Paste_Me_In_Query_Analyzer) and paste
it back into QA and run it

this is referred to as 'psuedo-dynamic sql' and it's demonstrated
further in a book called 'SQL Server 7 Secrets'

I personally can't wait for MS to support ADP in SQL 2005 with the
Acess 2007 release; then we should be able to change this to a simpler
try truncate catch delete from tblname

I think that should be a lot easier to deal with for things like this;
I just wish that MS would have held up their promise-- and released a
patch to make Access 2003 ADP work with SQL 2005.

-Aaron

Charles Wang[MSFT] · Dec 14, 2006

Hi Aaron,
Thanks for your appending suggestions.
Of course, "TRUNCATE" has better performance than "DELETE". I am sorry that
I did not think too much while I wrote this sample for Greg.
For your concerns, "I just wish that MS would have held up their promise--
and released a patch to make Access 2003 ADP work with SQL 2005." I
recommend that you give Microsoft feedback on this issue via the following
link:
https://support.microsoft.com/common/survey.aspx?scid=sw;en;1208&showpage=1&
ws=officeprodinfo&sd=offc
Your suggestions will be routed to Office team so that this feature can be
included in the next release of Access 2003.
Appreciate your understanding!

Charles Wang
Microsoft Online Community Support

aaron.kempf · Dec 14, 2006

reseed identity?

all you need is a truncate right; that does it automagically

-Aaron

Charles Wang[MSFT] · Dec 20, 2006

Hi Aaron,
I am glad to hear that you had resolved this issue. I am sorry that our
response was not in time for you. I checked the post time and it seemed
that it was caused by time difference. This issue was posted at Friday
evening at our time when we might have left company, so I gave you my
response at the next Monday. However during the time, you found your
resolution.
Appreciate your understanding on this. Thanks for using Microsoft Online
Managed Newsgroup. If you have any other questions or concerns, please feel
free to contact us.

Have a great day!

Charles Wang
Microsoft Online Community Support

Delete Data in All Tables

Guest

Charles Wang[MSFT]

David Portas

Charles Wang[MSFT]

aaron.kempf

Charles Wang[MSFT]

aaron.kempf

Charles Wang[MSFT]