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 |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2005-10-14 : 09:26:32
|
| Is there a simple way to drop all relation objects in a database? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-14 : 09:29:03
|
| Do you mean dropping Primary Key - Foreign Key relations?MadhivananFailing to plan is Planning to fail |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-10-14 : 09:33:44
|
| Yes |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-10-14 : 09:45:30
|
| Why do you want to do this?Before running this take a back up and if anything goes wrong restore itRun this in Query AnalyserSelect 'Alter Table '+table_name+ ' Drop constraint '+ Constraint_Name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS order by Constraint_TypeThen copy the results back to Query Analyser and run them one by oneMadhivananFailing to plan is Planning to fail |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2005-10-14 : 10:00:39
|
| Why? Because an import program needs to enter the data without the constraints.Thanks for the tip, but I think I will just use EM to delete them manually. That SQL also picked up my indexing constraints which I do not want to drop. Nice thing is that it does give me a list of what I need to look at, so thanks! |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-10-14 : 11:03:35
|
| http://www.sqljunkies.com/WebLog/amachanic/articles/ScriptTableConstraints.aspxThis is what you want.Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-14 : 11:39:36
|
| "Because an import program needs to enter the data without the constraints"I find that a bit scary Ken ... the constraints are there to stop the data getting out of whack ...If the imported data doesn't fit the constraints it shouldn't be imported.If the import route cannot present the data in the right order (i.e. the Foreign Keys get in the way of the sequence in which data can be imported) then I guess it would be OK to drop the FKs and recreate them after - but what if they won't create then? You'll have to restore from backup ... bit of a big job. This one I would want to work around - e.g. import into "staging" tables and then from there into the actual tables in a way that keeps the FKs "happy"!Kristen |
|
|
|
|
|
|
Drop all relations