CREATE TABLE myTable99(Col1 int PRIMARY KEY)
GO
CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
GO

INSERT INTO myTable99(Col1) SELECT 1
INSERT INTO myTable00(Col1, Col2) SELECT 1,2
GO

CREATE PROC myDynamicSQL99 @sql varchar(8000) AS EXEC(@sql)
GO

DECLARE @error int, @TABLE_NAME sysname, @sql varchar(8000), @x int, @rc int
SELECT @error = 0, @x = 0

DoItAgain:

DECLARE myCursor99 CURSOR 
    FOR 
	SELECT TABLE_NAME 
	  FROM INFORMATION_SCHEMA.Tables 
	 WHERE TABLE_NAME LIKE 'myTable%'
      ORDER BY TABLE_NAME DESC

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @TABLE_NAME

WHILE @@FETCH_STATUS = 0
  BEGIN
	SELECT @SQL = 'TRUNCATE TABLE ' + @TABLE_NAME
	SELECT @SQL
	EXEC @rc = myDynamicSQL99 @SQL
	SELECT @Error = @@Error
	SELECT 'Error Code: ' + CONVERT(varchar(15),@Error) + ' @rc: '+ CONVERT(varchar(15),@rc)  
	IF @Error <> 0
		SELECT @x = @x + 1
	FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
  END

CLOSE myCursor99
DEALLOCATE myCursor99

IF @x <> 0 
  BEGIN
	SELECT @Error = 0
	GOTO DoItAgain 
  END
GO

DROP PROC myDynamicSQL99
DROP TABLE myTable00
DROP TABLE myTable99
GO

quote:

---

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause.

---

quote:

---

Originally posted by eyechart

why don't you just disable all the constraints first.


-ec

---

declare @tblname sysname
declare @sqlstring varchar(500)
declare @sqlstring2 varchar(500)
declare @sqlstring3 varchar(500)
declare @loopcount int
declare @loopmax int
declare @err int

Create Table #Temp1
(ident int Identity,
 tablename sysname)


-- delete from tables that refer to other tables 


Insert #temp1 (tablename)
 select name 
 from sysobjects 
 where type = 'u' and 
	name <> 'dtproperties' and
	id not in (select rkeyid from sysforeignkeys)

Set @loopmax = (Select max(ident) from #temp1)
Set @loopcount = 1

While @loopcount <= @loopmax
Begin

Set @tblname = (Select tablename from #temp1 where ident = @loopcount)

	
	set @sqlstring2 = 'truncate table [' + @tblname + ']'
	print @sqlstring2
	exec (@sqlstring2)


	Set @err = @@error
	IF @err <> 0 
	Begin
		print 'error yo on table ' + @tblname + ' with error ' + @err
		

	End

Set @loopcount = @loopcount + 1
End

Truncate table #temp1

-- delete from tables that are referenced by other tables

Insert #temp1 (tablename)
 select name 
 from sysobjects 
 where type = 'u' and 
	id in (select rkeyid from sysforeignkeys)


Set @loopmax = (Select max(ident) from #temp1)
Set @loopcount = 1

While @loopcount <= @loopmax
Begin

Set @tblname = (Select tablename from #temp1 where ident = @loopcount)

	set @sqlstring = 'alter table ' + @tblname + ' noCHECK CONSTRAINT all'
	print (@sqlstring)
	exec (@sqlstring)
	set @sqlstring2 = 'delete [' + @tblname + ']'
	print @sqlstring2
	exec (@sqlstring2)
	set @sqlstring3 = 'alter table ' + @tblname + ' CHECK CONSTRAINT all'
	print (@sqlstring3)

	Set @err = @@error
	IF @err <> 0 
	Begin
		print 'error yo on table ' + @tblname + ' with error ' + @err
		
	End

Set @loopcount = @loopcount + 1
End

drop table #temp1

quote:

---

Originally posted by X002548

EDIT: Did a quick check...doesn't matter if it's disabled...still can't TRUNCATE....

---