I want to truncate all tables in a cursor. But I am unable to do it as some table are being reference by foreign key constraint

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

declare @table varchar(100)
declare NfrTable_cursor cursor
for
select  tableDetails from #temporaryTable
open NfrTable_cursor
fetch next from NfrTable_cursor into @table
WHILE @@FETCH_STATUS = 0
BEGIN

exec('select top 1 1 as found from ' + @table)
If @@RowCount > 0
begin
exec ('truncate table ' + @table)
end
fetch next from NfrTable_cursor into @table
end
close NfrTable_cursor
deallocate NfrTable_cursor

I am getting error as :

"Cannot truncate table 'BragDetail' because it is being referenced by a FOREIGN KEY constraint."

--How can I achieve this inside a cursor where the table name is dynamic.
-- I referred some sites,,but it is suggesting to drop the constraint,truncate and recreate the constraint.. but I am not sure how to do inside a cursor. Please help me in this.

Posted 28-Jul-14 23:15pm

anurag19289

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2014-07-28T23:26:00

Solution 1

You can truncate the table after turning off the foreign key constraint.
For e.g ALTER TABLE myTable NOCHECK CONSTRAINT myConstraint

Then enable the check constraint again.

However, note that you are actually corrupting your data by letting foreign key data lying around in another table.

Posted 28-Jul-14 23:26pm

Abhinav S

Comments

anurag19289 29-Jul-14 5:31am

Hi Abhinav,
quick question... How will I know myConstraint when I am looping inside cursor for a particular table ?

Abhinav S 29-Jul-14 5:43am

http://bhaveshgpatel.wordpress.com/2009/11/04/sql-server-list-all-constraints-of-database-or-table/

anurag19289 · Answer 2 · 2014-07-29T00:01:00

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSForEachTable "DELETE FROM ?"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

declare @table varchar(100)
declare NfrTable_cursor cursor
for
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
open NfrTable_cursor
fetch next from NfrTable_cursor into @table
WHILE @@FETCH_STATUS = 0
BEGIN
 DBCC CHECKIDENT (@table, reseed, 0); 
fetch next from NfrTable_cursor into @table
end
close NfrTable_cursor
deallocate NfrTable_cursor