Relational Database Systems

Generate Foreign Key References for all Tables – MS SQL Server

Following Script returns Foreign Key detail for all tables of selected database:


if object_id('tempdb..#tblFKReference') > 0
drop table #tblFKReference

create table #tblFKReference
(
Table_Name varchar(250),
TableReferencedBy varchar(7000),
ConstraintName varchar(250),
ColumnName varchar(250)
)

declare @tblName varchar(250)

declare cur cursor for select name from sysobjects
open cur

fetch cur into @tblName

while @@fetch_status = 0
begin

insert into #tblFKReference (Table_Name, TableReferencedBy, ConstraintName, ColumnName)
select
@tblName,
object_name(fkeyid),
object_name(s1.constid),
s3.name
from sysreferences s1
join sysobjects s2 on s1.fkeyid = s2.id
join syscolumns s3 on s2.id = s3.id and s1.fkey1 = s3.colid
where rkeyid = object_id(@tblName)
order by 1

fetch cur into @tblName

end
close cur
deallocate cur

select
Table_Name
,TableReferencedBy
,ConstraintName
,ColumnName
from #tblFKReference
order by Table_Name
 

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top