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
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.
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