Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eurob
Posting Yak Master
100 Posts |
 Posted - 2006-11-01 : 15:53:48
|
| I try to truncate a table (mytable) but get the msg that it cannot be truncated due to 'being referenced by a FOREIGN KEY constraint'. I removed all constraints in all tables that have any reference to 'mytable', but it keeps coming up with this msg. I might have missed one, sql server, however does not say which table are referenced though. Is there a statement that shows all foreign key constraints associated with a table ?robert |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-11-01 : 16:38:56
|
look up sp_help in BOL to select constraint info on the table. Also, review the differences between truncate and delete commands.Nathan Skerl |
 |
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-11-01 : 16:49:54
|
| I know how to pull the constraints from one table, sp_help indeed. I was just wondering if there is something that can loop through all the tables in a database, since I have about 200 tables.I need to do the truncate because I want to completely remove the contents and have it reset to the original seed value.If it doesn't work I can do DBCC CHECKIDENT(table,reseed,value).. but I was hoping to just do truncate robert |
|
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2006-11-01 : 17:04:00
|
| then why not just script the ddl of the database out and recreate all objects without data?seems like a lot of overhead to loop through all tables, dynamically create the ALTER statement to drop the constraints, then truncate all tables, then recreate the constraints somehow... sounds like all you want is an empty skeleton.Nathan Skerl |
|
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-11-01 : 17:12:41
|
| That's an option. It's rather intense though, I have to reset all the permissions and there are a lot of tables that have definitions in it that I don't want to remove. I just want to truncate a couple of tables that have user generated data in it. What I try to do is execute a DTS package that empties the tables that I selected and repopulate them with data from a test db. It moves all the data fine, however, it comes with 'cannot truncate table' message. Then my seed is off and I have gaps when adding new records. DBCC still works and solves the seed thing, I was just hoping to do this operation appropriately.robert |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
nathans
Aged Yak Warrior
938 Posts |
|
|
eurob
Posting Yak Master
100 Posts |
Posted - 2006-11-01 : 17:30:01
|
| That script is exactly what I was looking for...soo handy! Thanks all for the input.robert |
|
|
|
|
|
|
|
|
error msg when doing a truncate