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://www.SQLAuthority.com)
pinaldave
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′)
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
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