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
Thank you for these codes but i need your help seriously
The codes only display the column names but the foreign keys ar not displayed.
is there something i need to turn on?
k
Thank You.
thanks you also say rigth and help ful solution
@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);
i need small help
i had created different tables in my date base suppose let i have 5 tables in my data base with using primary key and foreign key now i have toknow in this five table which is my main table.?
what query i have to write for this one.
I want query of type given below
datatype,PKtable,PKColumn,FKtable,FKcolumn, constraintName
pls help me…….
Can we apply Foreign on two Column in Table in SQL
Very helpful information..Thank You Pinal
I have got one more query while doing Google. This query also gives me the foreign key for my database but I am getting different number of foreign key record from this query and from your query which is given above. My query is given below.
SELECT
FK.TABLE_NAME AS TableName,
CU.COLUMN_NAME AS ForeignKey,
PK.TABLE_NAME AS ReferenceTableName,
PT.COLUMN_NAME AS ReferenceColumnName,
C.CONSTRAINT_NAME AS 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
Please let me know which should I use.
Great Man Keep Rocking
This is a wonderful web site for beginners…
Really this is the SQL AUTHORITY :D
Baratam: When designing or working with a relational database, it is very good practice to create an entity relationship diagram (ERD) for your database. This would make it clear which are parent and which are child tables. If your database had only five tables, this could be done on one sheet of paper.
—————-
Jan: In:
AND NOT EXISTS (
SELECT OBJECT_NAME(i.object_id) AS tablename,
i.name AS indexname,
a.name AS columnname
FROM sys.indexes i
…. other joins
…..
— you only need
SELECT 1
, however when building that subquery, you might need to build a test version using all those columns (and since it is a correlated subquery, modify the test version to contain an extra INNER JOIN).
i need query to find child( foreign key tables) and child’s child tables ..
simply i want to implement recursive cte table in above query
i found out below query but i cant stop looping
;with child_list1(parent_table, child_table)
as
(
select o1.name as parent_table,o2.name as child_table
from sys.all_objects o1
join sys.foreign_key_columns f on o1.object_id = f.referenced_object_id
join sys.all_objects o2 on o2.object_id = f.parent_object_id
where o1.name = ‘work_order’ –= (select name from sys.all_objects where object_id = 711673583)
and o1.name o2.name
union all
select o1.name as parent_table,o2.name as child_table
from sys.all_objects o1
join sys.foreign_key_columns a on o1.object_id = a.referenced_object_id
join sys.all_objects o2 on o2.object_id = a.parent_object_id
inner join child_list1 cl on cl.child_table = o1.name
and o1.name cl.parent_table
and o1.name o2.name
and cl.parent_table o2.name
)
select * from child_list1
I’m getting the following SQL Exception Error:
System.Web.Services.Protocols.SoapException: Server was unable to process request. —> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint “FK_T_WEB_SUB_LINEITEM_TR_PRICE_TYPE”. The conflict occurred in database “Impresario”, table “dbo.TR_PRICE_TYPE”, column ‘id’. Error inserting into t_web_sub_lineitem The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Tessitura.WebAPI.Cart.AddPerformance(SeatRequest oSeatRequest) at Tessitura.WebAPI.Cart.Add(SeatRequest oSeatRequest) at Tessitura.WebAPI.Tessitura.ProcessSeatRequest(SeatRequest seatRequest) at Tessitura.WebAPI.Tessitura.ReserveTicketsDirect(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) at Tessitura.WebAPI.Tessitura.ReserveTickets(String sWebSessionID, String sPriceType, Int32 iPerformanceNumber, Int32 iNumberOfSeats, Int32 iZone) — End of inner exception stack trace —
Hi pinal
I have table which is referenced buy some other tables and those referenced tables also reference to some other table
now i want to delete entry from main table where i have not set on delete cascade with all foreign keys so if i will take your query in cursor and delete the entries in references table then error comes becoz we did not set tables order
so can u help
hi
i had a problem..
suppose a primary table had a foreign key with Secondary table.how can we know the data present in primary table is present secondary table or not?
You can do this
select case when s.col is null ‘not present’ else ‘present’ end as status from primary_table as p
left join secondary_table as s on p.key_col=s.key_col
TELL HOW TO ACESS MASTER TABLE USING FOREIGN KEY
the code is not providing me any output , it shows no output …does that mean there are no foreign keys in the database .
if you are running in correct database, then Yes.
it saves my time alot
Thank you so much