UPDATE : SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
This is very long query. Optionally, we can limit the query to return results for one or more than one table.
SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSÂ C
INNERÂ JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTSÂ FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNERÂ JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTSÂ PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNERÂ JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGEÂ CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNERÂ JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSÂ i1
INNERÂ JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGEÂ i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARYÂ KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
----Â optional:
ORDERÂ BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')
Reference : Pinal Dave (https://blog.sqlauthority.com)
136 Comments. Leave new
Really Nice Article… helped me alot :)
This is good but if I have multiple columns in my foreign keys it will multiply the results so that 2 column returns 4 rows of data and 3->9 and so on.
Adding the following line to Marks solution will reduce the amount of lines
AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION
Otherwise this was really helpful.
Excellent Note merix
Hi all,
Great script, really helpful. I was wondering, is there a way i can change this script so that I can get a sensible order of integrity?
Basically my problem is that we have this ‘sync’ service that packages data up from remote sites and sends it to our central database. At the moment it just pipes all the data in an manual order that we set because we know about the constraints. What i would like is to be able to know which tables need to have their data first (ie tables that have their primary keys referenced as foreign keys in other tables).
Is there a way to do that?
I needed a script which find all the table where primary key of a table is used in other tables as foreign key. i need to delete a record in master table ..
This script worked well for me.
thanks
suppose i have number of select statements in a single sp in sqlserver 2005.
i need to know how many statements are there in that sp.
hi…
i want to delete rows from tables having “UserID” Coulmn in a databse named “Test” . but some tables are foreignkey constraints… how it can be done…
first i have to find all tables having UserID column. then delete the rows from tables containg foreign key constraint(i.e where UserID=’SOMETHING’)…after that delete the tables having primary key..in a database…
SUMMARY:: I HAVE TO CLEAR ALL THE ROWS FROM ALL THE TABLES HAVING USERID COLUMN IN A DATABASE WHERE SOME ARE FOREGNKEY CONSTRAINTS AND SOME ARE PRIMARY KEY…
HOW IT’S POSSIBLE…
Hey How about
EXEC SP_Fkeys @TableName
Could we bind same default to multiple columns of the same table. If so then how.
PINAL, I have a somewhat related problem that i was wondering if you had a solution for
There are 4 tables with relationships to each other. ConferenceRoom, ConferenceRoomCapacity, TeleTrackPhone, and RPSLocation
The table ConferenceRoom stores attributes pertaining to conference rooms.
The table ConferenceRoomCapacity stores attributes pertaining to the maximum capacity of the conference room.
The table TeleTrackPhone contains different phone numbers. It stores ConferenceRoomID and RPSLocationID as FKs.
The table RPSLocation stoes info about different locations, city, state, etc.
The TeleTrackPhone table allows duplicate values for the ConferenceRoomID because a conference room can have more than one phone number.
This is the query that I run.
SELECT DISTINCT A.ConferenceRoomID, A.ConferenceRoomName, C.City, C.State
FROM ConferenceRoom As A, TeleTrackPhone As B, RPSLocation As C, ConferenceRoomCapacity As D
WHERE A.ConferenceRoomID = B.ConferenceRoomID
AND B.RPSLocationID = C.RPSLocationID
AND A.ConferenceRoomID = D.ConferenceRoomID
My problem is that it returns duplicates if the conference room has multiple phone numbers and i only want the query to return a list of conference rooms.
Nice one… It helped me to a lot during SQL Server Replication Setup
Hello,
This one is simpler, just gives you table_name and which type of key information and key name.
select OBJECT_NAME(PARENT_OBJ) TABLE_NAME, CASE WHEN XTYPE =’F’ THEN ‘FORIEGN KEY’ ELSE ‘PRIMARY KEY’ END KEY_TYPE , NAME KEY_NAME
from sysobjects where Xtype in (‘F’ , ‘pK’) ORDER BY XTYPE DESC
I am using only sysobjects table to get this information.
Hope this helps.
Imran.
Fantastic script, thank you so much!
Hi,
here’s another version (mine :-; ) to extract foreign keys.
Replace ‘TABLE_NAME’ with yours … !
Regards
Karim Laurent
select OBJECT_NAME(FKEYS.PARENT_OBJECT_ID) source ,PKCOLUMN_NAME= convert(sysname,col1.name), OBJECT_NAME(FKEYS.referenced_object_id) destination, FKCOLUMN_NAME = convert(sysname,COL2.name)
from
sys.columns COL1,
sys.columns COL2,
sys.foreign_keys FKEYS
inner join sys.foreign_key_columns KEY_COLUMN on (KEY_COLUMN.constraint_object_id = FKEYS.object_id)
where
COL1.object_id = FKEYS.referenced_object_id
AND COL2.object_id = FKEYS.parent_object_id
AND COL1.column_id = KEY_COLUMN.referenced_column_id
AND COL2.column_id = KEY_COLUMN.parent_column_id
AND OBJECT_NAME(FKEYS.PARENT_OBJECT_ID)=’TABLE_NAME’
Thanks, Great scripting
MAKE IT MORE SHORT N SIMPLE
Will work on both 2000 and 2005
===========================
select object_name(constid) FKey_Name, object_name(fkeyid) Child_Table, c1.name FKey_Col,
object_name(rkeyid) Parent_Table, c2.name Ref_KeyCol
from sysforeignkeys s
inner join syscolumns c1
on ( s.fkeyid = c1.id
and s.fkey = c1.colid )
inner join syscolumns c2
on ( s.rkeyid = c2.id
and s.rkey = c2.colid )
you are gr8 man
so simple and just fuzzy…. man
i mean wow…
Nice ..the script really helped me..!
Thanks..!
I want to retrieve only limited data from database like limit is keyword in mysql which retrieve limited data according to our arguments. Like I want to load only 10 records from the database at first display in page. but I don’t want to using top. Because its create problem of paging. So please give me alternate solution for that’d have use row index but in that row index I have to write inner query and my data base I s to heavy.
Excellent script that got me going in the right direction. Thank you so much.
Excellent script thank you
excelente, gracias por publicarlo.