How to get all reference of a column in a database.

digvijay dobhal

Dear sir Please explain me how i can get the reference of a primary key colun in a database. what exactly i want to do.... I have a table "[plan]" "PK column is PlanId" how many tables are using this column reference(foregin key reference) in mydatabase, i wana get these tables name. Waiting for your favorable reply. Regards Digvijay

Nagy Vilmos

There is not an easy answer here, it is down to your database schema. One way I would approach this is to buy a bottle of good scotch and take it to the DBA. Ask the DBA "what tables link to plan using planId?" If he can't help then you'll need to do some mining. First find the candidate field names. Not knowing which DB you are using I can give a generic idea:

SELECT DISTINCT COLUMN_NAME FROM SYSTEM_COLUMNS

Here the table SYSTEM_COLUMNS is the system table containing all the column definitions and COLUMN_NAME is the field with the column name. If you know the definition of the field planId you could add it into the query to narrow down the search. Once you have your fields, you can then query to get the tables that contain these fields. It is /relatively/ easy SQL but does need a bit of leg work to read through the results and ensure you have no false positives. If the table and column names do not follow a structure then I am afraid you may well have to use educated guesses.

Panic, Chaos, Destruction. My work here is done. Drink. Get drunk. Fall over - P O'H OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett

UNCRushFan

Could you please tell us what RDBMS you are using? Oracle? MySQL? Microsoft SQL Server? This will help us to better help you with an answer.

digvijay dobhal

Dera Sir, Thanks for your quick response. I'm using SQL server 2008 and SQL server 2008R2

Lost User

Since you mentioned in one of your comments that you are using SQL Server 2008, you can query the sys.foreign_keys table to get the information you want.

UNCRushFan

I second that suggestion. :cool:

digvijay dobhal

Thnks all finally i got.