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
This is exactly what I was looking for. Thanks a lot, Pinal !
hi,
I want to display the table which does not have foreign key.
Actually i have a db with 143 tables. Now i want to know the table which does not have any foreign key relationship as i have perform delete query on those tables.
Awaiting your response desperately.
Thank you
Hi,
i got the answer, i think this might help you.
— To display table does not having foreign key—
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
,t.name AS table_name
FROM sys.tables t
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.foreign_keys
WHERE type_desc = ‘Foreign_KEY_CONSTRAINT’ — or type = ‘PK’
–where type = ‘FK’
);
I have single database with three clients, I want to show the tables those related to that client instead of all tables?
You’re actually not very good at SQL are you Pinal? LOL.
this script is correct ?
use xyz
/*
Truncate All tables within a database
*/
Set NoCount ON
Declare @tableName varchar(200)
Declare @tableName1 varchar(200)
set @tableName=”
DECLARE @intFlag INT
Declare @count INT
–Find all child tables and those which have no relations
select ROW_NUMBER() over (order by t.table_name) sno, T.table_name
into #childtables
from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type =’Foreign Key’
or TC.constraint_Type is NULL) and
T.table_name not in (‘dtproperties’,’sysconstraints’,’syssegments’)
and Table_type=’BASE TABLE’ and T.table_name > @TableName
–Find all Parent tables
select ROW_NUMBER() over (order by t.table_name) sno,T.table_name
into #parenttables
from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where TC.constraint_Type =’Primary Key’
and T.table_name ‘dtproperties’and Table_type=’BASE TABLE’
and T.table_name > @TableName
–select * from #childtables
–select * from #parenttables
SET @intFlag = 1
SET @count = ( Select COUNT(*)as countValue from #childtables )
— Truncate All Child tables
WHILE (@intFlag <= @count)
BEGIN
Select @tableName1 = table_name from #childtables where sno = @intFlag
SET @intFlag = @intFlag + 1
Print @tableName1
Exec('Truncate table '+@tableName1)
print @tableName1 + ' truncated successfully '
END
SET @intFlag = 1
SET @count = ( Select COUNT(*)as countValue from #parenttables)
— Truncate All Parent tables
WHILE (@intFlag <= @count)
BEGIN
Select @tableName1 = table_name from #parenttables where sno = @intFlag
SET @intFlag = @intFlag + 1
Print @tableName1
Exec('Truncate table '+@tableName1)
print @tableName1 + ' truncated successfully '
END
Set NoCount Off
Optimized Code :
SELECT b.TABLE_NAME,d.COLUMN_NAME,c.Table_name,e.column_name,a.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE b
ON a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c ON
a.UNIQUE_CONSTRAINT_NAME=c.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE d ON
d.CONSTRAINT_NAME=a.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE e ON
a.UNIQUE_CONSTRAINT_NAME=e.CONSTRAINT_NAME
Hi,
Please suggest me simple query.
if two tables are there like:
create table sample(s_id int primary key,name varchar(20));
create table customer(s_id int foreign key references sample,remark varchar(10));
Now if i have to store data of ‘ABC’ having s_id=101,name=’ABC’,remark=’Good’.
then how will be the insert query for this?
???
Thanks so much for the script. Seems to get muddled on multi part foreign keys. I have added a few lines to match PK to FK on data type and max length which works perfectly for my purposes
SELECT K_Table = FK.TABLE_NAME ,
fk.TABLE_SCHEMA ,
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,
i1.TABLE_SCHEMA
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
INNER JOIN INFORMATION_SCHEMA.COLUMNS FKC ON fk.TABLE_NAME = fkc.TABLE_NAME AND fk.TABLE_SCHEMA = fkc.TABLE_SCHEMA AND cu.COLUMN_NAME = fkc.COLUMN_NAME
INNER JOIN INFORMATION_SCHEMA.COLUMNS PKC ON PT.TABLE_NAME = pkc.TABLE_NAME AND PT.TABLE_SCHEMA = pkc.TABLE_SCHEMA AND pt.COLUMN_NAME = pkc.COLUMN_NAME
WHERE fk.TABLE_SCHEMA = ‘dbo’
AND fkc.DATA_TYPE = PKC.DATA_TYPE AND fkc.CHARACTER_MAXIMUM_LENGTH = PKC.CHARACTER_MAXIMUM_LENGTH
ORDER BY 1, 6
Heya i am for the primary time here. I found this board and I in finding It really helpful & it helped me out much. I’m hoping to present something back and aid others such as you helped me.
Very useful script, thank you
Can any one explain me what you mean by Index seek and Index Scan?
@ Yogish,
Index Seeks are good comparing to Index Scans.
In Index Seeks, SQL Server query optimizer uses Indexes and as a result queries perform better. In Index Scans, SQL Server query optimizer uses indexes but ends up scanning all records of the index, this is called as Index scan. Index scans are bad.
There is tons and tons of material online on this topic.
Hi Pinal,
Please correct me if i am wrong.
Below query is also returning same result. Why do we need extra joins with
INFORMATION_SCHEMA.TABLE_CONSTRAINTS VIEW.
select
pk.TABLE_NAME ‘PK_Table’
, pk.COLUMN_NAME ‘PK_Column’
, fk.TABLE_NAME ‘FK_Table’
, fk.COLUMN_NAME ‘FK_Column’
, c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK on FK.CONSTRAINT_NAME = c.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK on PK.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
ORDER BY
1,2,3,4
nice, short and sweet.
hello pinal sir
i knew that seven type of backup in sql server 2005
i don’t what is use of seven type of backup and what is a query of seven type of backup and also possible of work in sql server express edition 2005
plz sir help me about this topic
Wonderful! Thanks, this is just what I’ve been looking for.
Dear sir and all friends
i have on request i knew sql server view, stored procedure and triggers. but i want to become sql administrator. plz help me about this topic log shipping, sql tuning,ssis package, mirror, 7 types of backup,cluster. i want to do practice of admin part. if any website than i want to learn or plz help me about this topic
advance thanks for pinal and madhivanan sir
THIS QUERY IS NOT CORRECT FOR RELATIONSHIPS WITH MORE THAN ONE FIELD!
FK_COLUMN is correct but it shows the first field for PK_COLUMN in all rows.
You know, I just spent several hours trying to fix this so it would display multiple field indexes correctly with no luck. I could sure use it if anyone can figure out how it is done.
GOT IT! (I think) Mark’s solution along with merix’s addition seems to handle all cases well. One of the solutions handled multiple fields but for some reason threw out a lot of the constraints. The code below works but I take NO credit for it.
SELECT
CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
TABLE_CATALOG = FK.TABLE_CATALOG,
TABLE_SCHEMA = FK.TABLE_SCHEMA,
TABLE_NAME = FK.TABLE_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME AND FK_COLS.ORDINAL_POSITION = PK_COLS.ORDINAL_POSITION
And, if you want to query off the sys tables rather than information_schema, this might be up your alley. It is narrow in scope (assumes “dbo” for schema, doesn’t list schema, etc), but it can certainly be enhanced to do so. It runs for one table at a time, but it gives both the parent key relationships, as well as other tables that are foreign keys to ‘YourTableName’.
SELECT Fk.name AS [Constraint],
object_name(Fk.parent_object_id) AS FK_table,
col_name(Fk.parent_object_id, Fk_Cl.parent_column_id)
AS FK_column,
TbR.name AS PK_table,
col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id)
AS PK_column
FROM sys.foreign_keys Fk
LEFT JOIN sys.tables TbR
ON TbR.object_id = Fk.referenced_object_id
JOIN sys.foreign_key_columns Fk_Cl
ON Fk_Cl.constraint_object_id = Fk.object_id
WHERE Fk.parent_object_id = object_id(‘YourTableName’)
OR Fk.referenced_object_id = object_id(‘YourTableName’)
ORDER BY CASE
WHEN object_name(Fk.parent_object_id) = ‘YourTableName’ THEN ‘0’
ELSE object_name(Fk.parent_object_id)
END,
CONVERT(int,
CASE
WHEN ISNUMERIC(REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)) = 0 THEN ‘0’
ELSE REPLACE(Fk.name, object_name(Fk.parent_object_id) + ‘FK’, ”)
END),
CASE
WHEN object_name(Fk.parent_object_id) = ‘item’ THEN TbR.name
ELSE object_name(Fk.parent_object_id)
END