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 (http://blog.SQLAuthority.com)
Excellenet Script
Perfect
REALLY HELPED ME.. GR8 SCRIPTING..
can there be multiple primary key (in multiple tables) in response to one foreign key in a table
Hi
Thanks for your query, it set me off in the right direction :).
Much Appreciated.
I have rewritten the query to be slightly faster:
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
Hope this helps :)
-Mark
Thanks… It helped me lot..
The linq version is:
var foreignKeys =
from rc in datacontext.REFERENTIAL_CONSTRAINTs
join tc in datacontext.TABLE_CONSTRAINTs
on new { rc.CONSTRAINT_CATALOG, rc.CONSTRAINT_SCHEMA, rc.CONSTRAINT_NAME }
equals new { tc.CONSTRAINT_CATALOG, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME }
where tc.CONSTRAINT_TYPE == “FOREIGN KEY”
join tc2 in datacontext.TABLE_CONSTRAINTs
//on rc.UNIQUE_CONSTRAINT_CATALOG equals tc2.CONSTRAINT_CATALOG
on new { cat = rc.UNIQUE_CONSTRAINT_CATALOG, sch = rc.UNIQUE_CONSTRAINT_SCHEMA, nam = rc.UNIQUE_CONSTRAINT_NAME }
equals new { cat = tc2.CONSTRAINT_CATALOG, sch = tc2.CONSTRAINT_SCHEMA, nam = tc2.CONSTRAINT_NAME }
where tc2.CONSTRAINT_TYPE == “PRIMARY KEY”
join kcu in datacontext.KEY_COLUMN_USAGEs
on rc.CONSTRAINT_NAME equals kcu.CONSTRAINT_NAME
join kcu2 in datacontext.KEY_COLUMN_USAGEs
on rc.CONSTRAINT_NAME equals kcu2.CONSTRAINT_NAME
select new
{
CONSTRAINT_NAME = rc.CONSTRAINT_NAME,
TABLE_CATALOG = tc.TABLE_CATALOG,
TABLE_SCHEMA = tc.TABLE_SCHEMA,
TABLE_NAME = tc.TABLE_NAME,
COLUMN_NAME = kcu.COLUMN_NAME,
REFERENCED_TABLE_CATALOG = tc2.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = tc2.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = tc2.TABLE_NAME,
REFERENCED_COLUMN_NAME = kcu2.COLUMN_NAME
};
slight error, this should be the correct one:
var foreignKeys =
from REF_CONST in datacontext.REFERENTIAL_CONSTRAINTs
join FK in datacontext.TABLE_CONSTRAINTs
on new { REF_CONST.CONSTRAINT_CATALOG, REF_CONST.CONSTRAINT_SCHEMA, REF_CONST.CONSTRAINT_NAME }
equals new { FK.CONSTRAINT_CATALOG, FK.CONSTRAINT_SCHEMA, FK.CONSTRAINT_NAME }
where FK.CONSTRAINT_TYPE == “FOREIGN KEY”
join FK2 in datacontext.TABLE_CONSTRAINTs
on new { cat = REF_CONST.UNIQUE_CONSTRAINT_CATALOG, sch = REF_CONST.UNIQUE_CONSTRAINT_SCHEMA, nam = REF_CONST.UNIQUE_CONSTRAINT_NAME }
equals new { cat = FK2.CONSTRAINT_CATALOG, sch = FK2.CONSTRAINT_SCHEMA, nam = FK2.CONSTRAINT_NAME }
where FK2.CONSTRAINT_TYPE == “PRIMARY KEY”
join FK_COLS in datacontext.KEY_COLUMN_USAGEs
on REF_CONST.CONSTRAINT_NAME equals FK_COLS.CONSTRAINT_NAME
join PK_COLS in datacontext.KEY_COLUMN_USAGEs
on FK2.CONSTRAINT_NAME equals PK_COLS.CONSTRAINT_NAME
select new
{
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 = FK2.TABLE_CATALOG,
REFERENCED_TABLE_SCHEMA = FK2.TABLE_SCHEMA,
REFERENCED_TABLE_NAME = FK2.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
};
Mark,
Thanks for sharing the information. It is important to learn and share. I am glad that you follow the same understanding.
Very good script.
Kind Regards,
Pinal
Hi Pinal,
Is there any other query to be written to show the hierarchial relationships between the tables exists in a DB. The DB diagram would show the logical representation but most of the times we dont have the prvileges to view.
So far i have worked in Oracle, it has the level pseudo column and a hierarchial query joining with dictionary table would give you all the details. In this we can filter with the table names. So that it would be easier to analyse the tables relation as a module wise.
Can you help me in this?
Regards
Mohamed
Hi Pinal,
Thanks for the query. Similar kind of queries i had written but with sys.foreign_keys views instead of REFERENTIAL_CONSTRAINTS.
Is there any other query to be written to show the hierarchial relationships between the tables exists in a DB. The DB diagram would show the logical representation but most of the times we dont have the prvileges to view.
So far i have worked in Oracle, it has the level pseudo column and a hierarchial query joining with dictionary table would give you all the details. In this we can filter with the table names. So that it would be easier to analyse the tables relation as a module wise.
Can you help me in this?
Regards
Mohamed
Hi Pinal,
Am a beginner in SQLServer and I find your script to be very helpful.
Do you have a similar script that lists all types of constraints, i.e. check constraints etc.?
Any pointers to the right direction will be very much appreciated.
Really fundoo, good that people like you believe in sharing information!
Hi Pinal,
I used this script to find duplicate relationships. Thank you for this information sharing.
Hey, this is really a good querry, We can use when we want to know that how many primary keys and forign keys are there in the database.
BUt as Subhash said that he used this querry to “find duplicate relationships” in the database.
Wht is mean by “duplicate relationships” ???
Hi,
What is the query used to display all tables names in sql server (Query analyzer) ?
Pls reply to this question? ASAP
thanks and regards
n.m.
Excellent, Very impressed
Hi there again
I was just re-looking at this script, and was wondering about something:
For the line in the script above that reads:
WHERE i1.CONSTRAINT_TYPE = ‘PRIMARY KEY’
Is this necessary, because don’t all foreign key constraints correspond to Primary Keys? In the REFERENTIAL_CONSTRAINTS table, the FK columns correspond to UNIQUE_CONSTRAINT…. columns. Can a FK correspond to any unique constraint or only a Primary Key constraint?
Thanks
Sorry, just to clarify the above comment:
The above mentioned where clause in your SQL is deftinately necessary because of the nested select statement.
Removing the CONSTRAINT_TYPE clause in my script above will not make it any faster.
I am just wondering what applies conceptually :)
hi every one
Thank you alot i am a bigenier in developing software
i want your advice to help me go in this field
Nice! However, I don’t think it’s working properly on multi-column foreign keys.
This is not a comment rather question.
I went through the above post and found very interesting. I tried to modify it according to my problem but no luck.
Problem.
how to get the list of tables who has the FK relation ship with the given tables. once i get the list I want to delete the record from the parent table and set the references to null from the table where parents pk is referred as fk.
I am not using cascade delete since I don’t want to delete the complete row.
right now i am doing this by writing the simple SP and manually setting the null to the referenced tables.
Thanks you very much in advance.
-Santosh Maskar
after spending some time on the given SQL I solved my problem.
Thanks a lot
-Santosh
hi
I need to delete the paticular records from parent table without affecting the child table(will have more child table) and also the relationship between the records should be removed,
since am going to use the unaffected record in child table as parent table..
Hope u got it..
Your help is appreciated.
Thanks & Regards
Bala
Thank for the script. It really help me.
Thank
Regards.
Johnson
This was a great tutorial! It was no problem at all to adapt this and create a routine that automatically fixed that pesky name mangling the occurs in Sql Server constraints ( so that FK__Trader__departme__4B7734FF becomes FK__Trader__department_id).
You helped me solve a real headache.
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.
Hi,
It was great, it helped me in finding the duplicate FK for the same set of tables for the same set of fields.
With Regards
Dakshina Murthy
Hi
How to retrieve all columns of the given table and its foreign key table.
Regards
Jaseem, A.
This script is amazing, it saved my life.
You guys rock! This has saved me sooo much time. Thanks all
Good script !!! saved lot time.
Excellent site.
The queries above (except for Smeet’s) return extra incorrect rows for compound FKs.
I ran a SQL Profiler trace while scripting out a compound FK in Management Studio and here is what I found in the Profiler trace. The result additionally outputs an ID column that displays the correct order in which the PK table columns are referenced.
Cheers
select tbl.name as [Table_Name] ,
cstr.name as [ForeignKey_Name],
fk.constraint_column_id as [ID] ,
cfk.name as [Name] ,
crk.name as [ReferencedColumn]
from sys.tables as tbl
inner join sys.foreign_keys as cstr
on cstr.parent_object_id=tbl.object_id
inner join sys.foreign_key_columns as fk
on fk.constraint_object_id=cstr.object_id
inner join sys.columns as cfk
on fk.parent_column_id = cfk.column_id
and fk.parent_object_id = cfk.object_id
inner join sys.columns as crk
on fk.referenced_column_id = crk.column_id
and fk.referenced_object_id = crk.object_id
order by [Table_Name] asc ,
[ForeignKey_Name] asc,
[ID] asc
Very nice script, it is really helpful.
once again your SQL scripts have proven to be incredibly useful.
Thank you!
Excellent script
than kyou
it hepls me a lot.
I have question
i have several(around 70) tables.
Person table is the primary table.personId is the primary key in this table. eventhough primary key is unique I have several duplicate rows in the primary table(like all fields are the same except personID[primarykey]).
I want to delete duplicates from the primary key table and same time I want to update that foreign key with the other in the foreign key table.
could u help please help me regarding this.
thank u
Hello Usha,
There is no inbuilt functionality to perform the complete task straightway. You will have to write a batch to complete it. Here I can provide you a guideline to implement.
Suppose the design of your Person table is as following:
PersonID Name Age
1 ABC 30
2 XYZ 35
3 ABC 30
Here you want that in all foreigh key tables update the PersonID 3 (duplicate record) to 1 and then from Person table delete the record where PersonID is 3.
At first Get the minimum PersonID for all duplicate records using following script:
WITH CTE (PersonIDOld, PersonIDNew, DuplicateCount)
AS
(
SELECT PersonID as PersonIDOld, MIN (PersonID) OVER(PARTITION BY Name, Age) AS PersonIDNew,
ROW_NUMBER() OVER(PARTITION BY Name, Age ORDER BY Name, Age) AS DuplicateCount
FROM Person
)
SELECT PersonIDOld, PersonIDNew INTO #temp
FROM CTE
WHERE DuplicateCount > 1
Note: Here replace the column Name, Age with all columns of Person table except Primary Key column.
Now update foreign key of PersonID in all foreign key tables as following:
UPDATE FKTable1
SET PersonID = PersonIDNew
FROM FKTable1 INNER JOIN #temp tmp
ON FKTable1.PersonID = tmp.PersonIDOld
At last delete all duplicate records from Person table as following:
DELETE Person
FROM Person INNER JOIN #temp tmp
ON Person.PersonID = tmp.PersonIDOld
Please let me know if you need more clarification or if you have any issue.
Kind Regards,
Pinal Dave
thank you so much sir
I think it will workIi am beginner to sql
I don’t know how to create and run batch
could you help me reagrdng this
SELECT PersonIDOld, PersonIDNew INTO #temp
FROM CTE
WHERE DuplicateCount > 1
I executed until this as a query it shows 392 rows affected.
I try to update the foreginkey query
but it is not recognizing
PersonIDNew,
PersonIDOld
could u help me regarding this.
————————————————
and I have one more question
can I update all the foreign key tables at a time and delete the duplicates from Primary key table.
Can I run all this asa one batch
—————————————————
I have foreign key tables iin different databases also
I am totally using 3 databases
I need to update the foreign key’s from all those tables based on this primary key
————————————————————————
and one condition i need to check
in some tables this foreign key is also used as a primary key
at that time it shows a error message like
sequence contains more than one element
when I try to oupdate that foreign key with new value
—————————————————
actually I wrote queries and try to access those through C#.net
If u provide any direct solution through sql server or through C# programming is good for me.
———————————————–
It is my first job I am working on this from last 3 weeks.
If u provide any solution to this, I am really thank ful to you
I appreciate it
could any one reply to my question,plese?
i am relly waiting for the suggestions.
I trie the CTE query
it
IT is working fine
thank u som much
but now I had another probem like
when i try to update foreign key tables
some tables are updating
but some tables are shows a message like
Msg 2627, Level 14, State 1, Procedure T_UPD_Membership_Question, Line 14
Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
The statement has been terminated.
even I tried by removing relaton ships.still it won’t allow me
please help me regrding this.
Hello Usha,
The error “Violation of PRIMARY KEY constraint ‘PK_Membership_Question’. Cannot insert duplicate key in object ‘dbo.Membership_Question’.
” occures when duplicate vaues are inserted in Primary key column.
Make sure that foreign-key column is not primary-key or a part of primary-key in foreign-key table. If that is the case than you will have to drop the Primary-key constraint from foreign-key column in foreign-key table.
Kind Regards,
Pinal Dave
thank u so much Mr. Dave
I did try that
for some tables it is working fine
but for some tables it repetedly displays same message.
is there any thing like even after i deleting the primary key and disabling triggers and unique indexes.
can i check the entire row before updating
if it isalready there then deleete the row and insert the new one which I wnat to update.
or any other suggestions regarding this.
thnk you
Sir i have a Problem statement given below plz canu help me of making query of this type of statement:
Using the data dictionary of Oracle server, write down a single SQL statement to retrieve the parent and child relationship between all the tables of the currently connected schema.
Sample output from HR schema:
Employees ———hasParent———>Departments
Departments ——-hasParent———>Locations
Locations———hasParent———>Countries
Countries———hasParent———>Regions
Hi
folloing query is running sucessfully second one is not running
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
regards
Jayant
Wow great it helped me a lot….
Thankx
Fantastic Blog… I always find it very useful..
I have a question , hopefully you can help. I’d like to include views in this script. I’m querying Microsofts CRM system and can only use views, it’s been heavily customised and looks pretty confusing.
What I’d like to see is the view name and column name where the column within the view is a key column. For example If we have an Account table with a primary key of AccountID and there are another 5 tables with a foreign key relationship back to the account table. I’d like to see any views referencing these 5 tables that contain the foreign key column in the view defintion… Hope that’s clear. I’d be very grateful if you could offer some help..
Regards
Paul
Hi Mr. Pinal Dave,
Is it possible to combine your query (the one shown above) with this one as to get a single file at once.
What I’d like to do is to build the [(DisplayName ...... )] of the second part of the query with the results of the query you have provided here as to build a single file.
select vrstica from
( SELECT t.name as tabela, as sort, ‘[MetadataType(typeof(' + t.name + 'Metadata))]‘
+ ‘ public partial class ‘ + substring(t.name, 1, len(t.name)-1) + ‘ {[DisplayName("' + t.name +'")]‘
+ ‘ public class ‘ + t.[name] + ‘Metadata{‘ as vrstica FROM sysobjects t WHERE t.xtype=’U’
union all
SELECT sysobjects.name, 2, ‘ [DisplayName("' + replace(syscolumns.name,' ','_') + '")]‘
+ case syscolumns.isnullable when 0 then ‘ [Required()]‘ else ” end
+ ‘ public object ‘ + replace(syscolumns.name,’ ‘,’_') + ‘{get;set;}’
+ ‘ //’ + systypes.name
+ ‘(‘ + ltrim(rtrim(str(isnull(syscolumns.prec,0)))) + ‘,’ + ltrim(rtrim(str(isnull(syscolumns.scale,0)))) + ‘)’
+ ‘ ‘ + ltrim(rtrim(str(syscolumns.length)))
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype = systypes.xtype and systypes.name not like ‘sysname’
WHERE sysobjects.xtype=’U’
union all
SELECT t.[name], 3, ‘}}’
FROM sysobjects t
WHERE t.xtype=’U’
) t
ORDER BY tabela, sort
Thanks in advance
Carlos Porras (El Salvador)
Hi!
Use Pinal’s beautiful script to list all PK and FK keys in a DB/table
SELECT DISTINCT
KeyColName = PT.COLUMN_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
–Optional – to show only one/limited list of tables
–WHERE i1.TABLE_NAME IN (‘object_registry’)
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY PT.COLUMN_NAME
BR
/witecat
Is there any query which will show all the tables which is referenced by the table Employee
Thanks
Hello Rajan,
Use sp_depends store procedure. It will list all objects that reference and that are referenced by specified table.
Regards,
Pinal Dave
Hi, I have 2 tables (Staff and Family Mem) with a one to many relationship..
In Family Mem table, one family member can belong to only one staff, while staff can have more than one family mem.
In staff table, staffid is the PK while staffid is the FK in the family table.
I would like to know the query/way to relate these two tables using foreign key if I am using XAMPP?
And how should I write the INSERT statement to insert into both tables at once since the staff’s registration form also contain data that belongs to the family mem table…
PLS help… Thxxxx so much…
Hi
I find your scripts really helpful!
i developed this shorter one from some of your codes:
SELECT K.TABLE_NAME AS TableName, K.COLUMN_NAME AS ColumnName, OBJECT_NAME(S.referenced_object_id) AS RefTableName,
COL_NAME(S.referenced_object_id, S.referenced_column_id) AS RefColumn, K.CONSTRAINT_NAME AS ConstraintName
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K INNER JOIN
sys.foreign_key_columns AS S ON K.TABLE_NAME = OBJECT_NAME(S.parent_object_id)
WHERE (K.TABLE_NAME = @TableName)
ORDER BY ConstraintName DESC
I want the PK and all FKs in the table. I get it but it returns them in duplicate lines. Can you help me narrow to single line results?
thanks
@M
Great script! I believe this is the only one mentioned here that returns the correct results for fks referencing non-primary, unique-key columns. Also it works with linked servers so I can look for missing fks on production by comparing to development.
Thanks!
This was great one!!!
Hi Pinal, this is a seriously good script and it must feel great that you have helped so many people over such a long period of time.
Thanks!
Hello,
the above script has a bug. When an FK consists of two columns, it produces a cross-product of four rows for that FK, not two rows.
I still need to analyze the query to understand and fix it.
Greetings, Christoph
Pinal,
There are a couple of corrections needed to your query; it does not handle FK’s linked through unique constraints, and it does not handle multi-column keys well. Also, there was no need for the in-line ‘pt’ table. Here is he revised query:
SELECT
FK_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
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcu
ON rc.UNIQUE_CONSTRAINT_NAME = pkcu.CONSTRAINT_NAME
WHERE
fkcu.ORDINAL_POSITION = pkcu.ORDINAL_POSITION
Even with all the above changes, the query will not be able to handle FK relationships linked to an independantly created unique index (i.e. index created using ‘create index’ statement).
Pinal ,
how do i find the relation ship between attributes in a table ,
i need to write a query which runs on a table
(table name will be passed as the parameter to the query )
and tel me the relationship between the primary key column and the other columns in the same table , result should tell me the relationship is whether its 1:1 or 1:m or M:1 or M:M taking primary key as the granular level
Regards,
Ashok
Start with
EXEC sp_help ‘table_name’
Thank you Dave, you’re now one of my heroes !!
Terrific script, helped me a lot modeling an existing DB. Thanks for sharing!
for the scripts below, where do i put my table name and colum names
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 )
Edit: Added constraint type
select tbl.constraint_name
, tbl.constraint_type
, tbl.table_schema
, tbl.table_name
, col.column_name
, col3.table_schema as ref_table_name
, col3.table_name as ref_table_name
, col3.column_name as ref_column_name
from information_schema.table_constraints tbl with(nolock)
left join information_schema.constraint_column_usage col with(nolock) on tbl.constraint_name = col.constraint_name
left join information_schema.referential_constraints col2 with(nolock) on tbl.constraint_name = col2.constraint_name
left join information_schema.constraint_column_usage col3 with(nolock) on col2.unique_constraint_name = col3.constraint_name
order by tbl.table_name
thanks for all Examples!
pretty good
hi guys….
create table emp
(e_id int primary key,
e_name varchar(255))
insert table emp values(1,’aa’);
insert table emp values(2,’bb’)
then i created new table:
create table dept
(d_id int primary key,
d_name varchar(255),
e_id int foreign key references emp(e_id))
with this st. it works well:
insert table dept values(11,’computer’,1);
this st. creats problem:
insert table dept values(33,’marketing’,3);
so plz help on this problem
error for the above problem:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint “FK__t22__e_id__4FD1D5C8″. The conflict occurred in database “master”, table “dbo.t11″, column ‘e_id’.
The statement has been terminated.
It is becuase you didn’t have e_id 3 in the emp table
hi
madhivanan,
i know that ,but my question is i have created one table with primary column but when i create another table with foreign key of references to first table primary column. when i insert data it does not except it and give above error….
plz let me know were i m going wrong
thanks in advances
Excellent query, exactly what I needed – thanks!
Very handy ! Thanks!
- Ferruccio Guicciardi
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
[...] Query to Display Foreign Key Relationships and Name of the Constraint for Each Table in Database My blogging journey began with this blog post. As many of you know my journey began with creating a repository of my scripts. This was very first script which I had written to find out foreign key relationship and constraints. The same query was updated later on using the new SYS schema modification in SQL Server. [...]
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