While writing articles based on my Find Table without Clustered Index – Find Table with no Primary Key I got an idea about writing this article. I was thinking if you can find primary key for any table in the database, you can sure find foreign key for any table in the database as well. If you have database and it is not forcing a database relationship, I suggest you look at your design once again. Relational database without keys and connection, may not make sense at all.
The keys are very, very critical to any database system as they are the one who enforces the system
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?
Find Tables With Primary Key Constraint in Database
Please leave a comment right below.
Reference: Pinal Dave (https://blog.sqlauthority.com)
48 Comments. Leave new
I’m curious why you chose this path instead of utilizing information_schema? What’s the benefit, the down-side?
/*** 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 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
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
It’s really very good script.it is very unique and useful script.keep it up pinal.You are doing very well job.
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…