0

Is there a way to find all table names and constraint names referencing given column? I am using MS SQL server database. I have tried several solutions proposed on StackOverflow, e.g.
Find referenced field(s) of foreign key constraint
Get all referencing columns to a referenced column in linq
MSSQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?
But my database is very large - about 600 tables, so running SQL queries suggested as solutions in above links just take more than 15 min.
Btw. the column in question is probably referenced trough many, many tables and checking this manually seems to take a lot of time. I am trying to put down those constraints (e.g. ALTER TABLE table NOCHECK CONSTRAINT table_constraint_1;) so I could delete data from the table they all are referring to, then insert new data and put up again constraints and see what happens, i.e. fix things for everything to wrok.
Does anybody have any better proposal?

Thanks in advance,
every help is very much appreciated.

mismas

Community
  • 1
  • 1
mismas
  • 1,236
  • 5
  • 27
  • 55
  • Since this probably isn't something you'll run very often, I don't see what's wrong with it taking 15 minutes. If you are going to run it very often, then you're doing something wrong: you have no control over your database and that's a bad thing. – simon at rcl Apr 22 '14 at 14:26
  • Have you tried INFORMATION_SCHEMA(s) ? select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS – Ondrej Svejdar Apr 22 '14 at 14:27
  • @simonatrcl I said it was more than 15 min. I didn't actually get to see the end of it, i.e. don't know how much it actually needs to be done (that is my problem). It seems to me it is taking far too long. I could try running it and leaving it to run for half an hour, an hour, hours ... and see. I was just wondering if anybody had some better proposal. Btw. it is a legacy system which is being modified, and unfortunately db design is not its strongest side ... – mismas Apr 22 '14 at 14:39
  • OK. I have worked with legacy DB's of similar sizes. However, a) I made sure I was on a copy of the Production DB and b) never run this sort of query on Production. Having said that, then, set your query off before you go home; who really cares how long it takes? I once developed a set of queries one of which initially took 20 hours to run. I had to let it run to completion so I could see how much it needed to improve, and whether it returned the right data. Eventually I got it down to 20/30 minutes which the user was happy with. Don't worry about long queries - especially one-offs like this. – simon at rcl Apr 22 '14 at 14:43
  • Sorry guys it was my mistake. I was running my queries in a transaction and did not rollback it, so executing those queries took a while :) Sorry again! – mismas Apr 22 '14 at 14:44
  • http://stackoverflow.com/questions/11866081/mssql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular worked like a charm! – mismas Apr 22 '14 at 14:53

0 Answers0