While writing article based on my SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key I got idea about writing this article. I was thinking if you can find primary key for any table in database you can sure find foreign keys for any table in database as well.
In SQL Server 2005 How to Find Tables With Foreign Key Constraint in Database?
Script to find all the primary key constraint in database:
USE AdventureWorks;
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database?
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
Reference : Pinal Dave (http://blog.SQLAuthority.com)
I’m curious why you chose this path instead of utilizing information_schema? What’s the benefit, the down-side?
Hi Catherine,
There are different way to query system data. Information_schema and catalog views are two major method.
As per Microsoft:
“Catalog views return information that is used by the Microsoft SQL Server 2005 Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.”
http://msdn2.microsoft.com/en-us/library/ms174365.aspx
“Some changes have been made to the information schema views that break backward compatibility. These changes are described in the topics for the specific views.”
http://msdn2.microsoft.com/en-us/library/ms186778.aspx
Looking at both of them, I have decided to go for catalog views. There are few additional advantages are there to use catalog views.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
[...] UPDATE : SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database [...]
/*** Script all Foreign Key Constraints ***/
/*** The Result Set can be used to copy constraints to your testing DB or to keep on hand in case of errors. ***/
SELECT
‘ALTER TABLE ‘+FK.TABLE_NAME+
‘ ADD CONSTRAINT ‘+C.CONSTRAINT_NAME+’ FOREIGN KEY’+
‘(‘+CU.COLUMN_NAME+’) ‘+
‘REFERENCES ‘+PK.TABLE_NAME+
‘(‘+PT.COLUMN_NAME+’)’ ForeignKeyScripts
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
–WHERE PK.TABLE_NAME IN (‘Table1′, ‘Table2′)
–WHERE FK.TABLE_NAME IN (‘Table1′, ‘Table2′)
Hi Pinal Sir
Suppose Order Table has primary key
& Order table is connected to OrderDetail, Employee (relation one to many)
So from Order Table I want to identify thaat with which table it has associations (one to many relations) ?
the query should give answer OrderDetail, Employee
How to write a SQL script to find those tables in which foreign key constraints are missing.
hello,
I like have script that lists out all the fk contsraints in sql server 200.
Thanks In ADV,
Jack
Hi
@Milli,
This can be only done manually analyzing the data.
@Jigar,
I only support SQL Server 2005.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Pinal,
I have a requirement where in for a given View, I need to find out all the tables that are used in the view and I also need to find out how each table in the view is linked.
For example, if I create a simple view like
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.ID = TableB.FKID
INNER JOIN TableC ON TableA.ID = TableC.FKID
I need to find out all the Tables involved in the view i.e. TableA, TableB and TableC. I also need to find out how TableA and TableB, TableA and TableC are linked (i mean the join Condition).
Could you please point me to resources that can help me find the answer to this.
Thanks
Prasad
@jigar:
SELECT sfk.fkeyid, sof.name AS fTableName, sor.name AS rTableName, scf.name AS fColName, scr.name AS rColName
FROM dbo.sysforeignkeys sfk INNER JOIN
dbo.sysobjects sof ON sfk.fkeyid = sof.id INNER JOIN
dbo.sysobjects sor ON sfk.rkeyid = sor.id INNER JOIN
dbo.syscolumns scf ON sfk.fkey = scf.colid AND sof.id = scf.id INNER JOIN
dbo.syscolumns scr ON sfk.rkey = scr.colid AND sor.id = scr.id
I have written a script which I believe will list all the Foreign Key constraints that are missing supporting indexes. But since I’m very inexperienced with SQL Server I was hoping I could throw this over the fence and let the big dogs chew on it.
Thanks in advance for any and all criticisim.
George
select C.Table_Name,
C.Constraint_Name,
C.Constraint_Columns
from
(select object_name(i.object_id) table_name,
i.name index_name,
max(case index_column_id when 1 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 2 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 3 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 4 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 5 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 6 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 7 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 8 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 9 then col_name(ic.object_id,ic.column_id) else ” end)+
max(case index_column_id when 10 then col_name(ic.object_id,ic.column_id) else ” end) index_columns
from sys.index_columns ic,
sys.indexes i
WHERE ic.index_id = i.index_id
AND ic.object_id = i.object_id
AND OBJECTPROPERTY(i.OBJECT_ID,’IsUserTable’) = 1
AND i.index_id != 1
GROUP BY i.object_id, i.name) as I RIGHT OUTER JOIN
(select kcu.table_name,
kcu.constraint_name,
max(case kcu.ordinal_position when 1 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 2 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 3 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 4 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 5 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 6 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 7 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 8 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 9 then kcu.column_name else ” end)+
max(case kcu.ordinal_position when 10 then kcu.column_name else ” end) constraint_columns
from information_schema.key_column_usage kcu,
information_schema.referential_constraints rc
where rc.constraint_name = kcu.constraint_name
group by kcu.table_name, kcu.constraint_name) as C
on C.Table_Name = I.Table_Name
and I.Index_Columns like C.Constraint_Columns + ‘%’
where I.Table_Name is null
Hi Pinal,
Your articles are always great source of information.
Kind Regards,
Azim
i want to know the Foreign key name between two tables.
Hi Pinal,
Your articles are always great source of information. Can u provide code i mean query to add FK on new table
Thanks
Tom
@TOM i m writting query to create table with fk.
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
Hi Pinal
Very interesting article and of great help.
I made a little addition to your code. As I wanted also to know what the FKs are doing in the Table (referential integrity on update and on delete) I added two columns to your superb query. And a little bit of ordering :)
Maybe it helps other readers.
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName,
f.update_referential_action_desc AS UpdateAction,
f.delete_referential_action_desc AS DeleteAction
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
ORDER BY TableName ASC, ColumnName ASC
[...] Joffery has provided nice script which is modification to previous article of SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database. [...]
simply superb! gr8 work!
George,
I’ve a script that does the same but is a little less complicated I think:
SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN sys.foreign_keys f
ON CCU.TABLE_NAME = object_name(parent_object_id) and CONSTRAINT_NAME = f.name
WHERE f.type = ‘F’
AND NOT EXISTS (
SELECT OBJECT_NAME(i.object_id) AS tablename,
i.name AS indexname,
a.name AS columnname
FROM sys.indexes i
INNER JOIN sys.index_columns c
ON i.object_id = c.object_id AND i.index_id = c.index_id
INNER JOIN sys.all_columns a
ON a.object_id = c.object_id AND a.column_id = c.column_id
WHERE CCU.TABLE_NAME = OBJECT_NAME(i.object_id)
AND CCU.COLUMN_NAME = a.name
)
ORDER BY CCU.TABLE_SCHEMA, CCU.TABLE_NAME, CCU.COLUMN_NAME
Hi, ur articles always found very useful. thanks. I hv one query.. i hv constraint on my master table, but there are excess data in my master, which not yet used in sub tables. I want to delete master data which is not used in subtable. Is it possible to delete thru sql? i m using sql 2005.
thanks
[...] SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database [...]
[...] Key and Foreign Key relationship using T-SQL, I came across my own blog article written earlier SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database. It is really handy script and not found written on line anywhere. This is one really unique script [...]
It’s really very good script.it is very unique and useful script.keep it up pinal.You are doing very well job.
[...] Related Post: SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database [...]
Dude that rocks! You saved me a lot of frustation. Thank you. Thank you.
Excellent query. I was thrown into the project to work on somebody else’s database design. I was supposed to right a data export procedure and this script CAME SO HANDY so find all the keys and the to right the stored procedure that return the data i want. Iit would have been impossible to find the relationships and then develop the procedure. AWESOME AWESOME!!!!!!!!!!!!!!
Hello,
How can i get unique key constains of table1.
Thanks a lot, I needed that!
Thank you, not only for this post, but for your entire blog, it helped me a lot…
Thank you for these codes but i need your help seriously
The codes only display the column names but the foreign keys ar not displayed.
is there something i need to turn on?
k
Thank You.
thanks you also say rigth and help ful solution
@TOM i m writting query to create table with fk.
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
i need to know the list of keys used in SQl server 2005.. plz help me anybody…
i need small help
i had created different tables in my date base suppose let i have 5 tables in my data base with using primary key and foreign key now i have toknow in this five table which is my main table.?
what query i have to write for this one.
I want query of type given below
datatype,PKtable,PKColumn,FKtable,FKcolumn, constraintName
pls help me…….
Can we apply Foreign on two Column in Table in SQL
Very helpful information..Thank You Pinal
I have got one more query while doing Google. This query also gives me the foreign key for my database but I am getting different number of foreign key record from this query and from your query which is given above. My query is given below.
SELECT
FK.TABLE_NAME AS TableName,
CU.COLUMN_NAME AS ForeignKey,
PK.TABLE_NAME AS ReferenceTableName,
PT.COLUMN_NAME AS ReferenceColumnName,
C.CONSTRAINT_NAME AS 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
Please let me know which should I use.
Great Man Keep Rocking
This is a wonderful web site for beginners…
Really this is the SQL AUTHORITY :D
Baratam: When designing or working with a relational database, it is very good practice to create an entity relationship diagram (ERD) for your database. This would make it clear which are parent and which are child tables. If your database had only five tables, this could be done on one sheet of paper.
—————-
Jan: In:
AND NOT EXISTS (
SELECT OBJECT_NAME(i.object_id) AS tablename,
i.name AS indexname,
a.name AS columnname
FROM sys.indexes i
…. other joins
…..
– you only need
SELECT 1
, however when building that subquery, you might need to build a test version using all those columns (and since it is a correlated subquery, modify the test version to contain an extra INNER JOIN).
i need query to find child( foreign key tables) and child’s child tables ..
simply i want to implement recursive cte table in above query
i found out below query but i cant stop looping
;with child_list1(parent_table, child_table)
as
(
select o1.name as parent_table,o2.name as child_table
from sys.all_objects o1
join sys.foreign_key_columns f on o1.object_id = f.referenced_object_id
join sys.all_objects o2 on o2.object_id = f.parent_object_id
where o1.name = ‘work_order’ –= (select name from sys.all_objects where object_id = 711673583)
and o1.name o2.name
union all
select o1.name as parent_table,o2.name as child_table
from sys.all_objects o1
join sys.foreign_key_columns a on o1.object_id = a.referenced_object_id
join sys.all_objects o2 on o2.object_id = a.parent_object_id
inner join child_list1 cl on cl.child_table = o1.name
and o1.name cl.parent_table
and o1.name o2.name
and cl.parent_table o2.name
)
select * from child_list1
[...] Version 1: Using sys.schema [...]
I’m getting the following SQL Exception Error:
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_T_WEB_SUB_LINEITEM_TR_PRICE_TYPE”. The conflict occurred in database “Impresario”, table “dbo.TR_PRICE_TYPE”, column ‘id’. Error inserting into t_web_sub_lineitem The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Tessitura.WebAPI.Cart.AddPerformance(SeatRequest oSeatRequest) at Tessitura.WebAPI.Cart.Add(SeatRequest oSeatRequest) at Tessitura.WebAPI.Tessitura.ProcessSeatRequest(SeatRequest seatRequest) at Tessitura.WebAPI.Tessitura.ReserveTicketsDirect(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) at Tessitura.WebAPI.Tessitura.ReserveTickets(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) — End of inner exception stack trace —
Hi pinal
I have table which is referenced buy some other tables and those referenced tables also reference to some other table
now i want to delete entry from main table where i have not set on delete cascade with all foreign keys so if i will take your query in cursor and delete the entries in references table then error comes becoz we did not set tables order
so can u help
Thanks for the post..
I was reading:
http://blog.sqlauthority.com/2013/03/25/sql-server-query-writing-strategy-sql-queries-2012-joes-2-pros-volume-1-the-sql-queries-2012-hands-on-tutorial-for-beginners/
It was hard to find out the foreign keys with respect to a primary key column.