A Primary & Foreign Key Retrieval Stored Procedure

Here is the stored procedure which will retrieve the primary and foreign key and its reference for a database in SQL Server. This is also an example for a stored procedure with cursors and temporary table.

create proc sp_GetMasterConstraints(@tabnm nvarchar(128),@flag nvarchar(128)='')
as

declare @reftabid int
declare @constid int
declare @refcolid int
declare @colid int

declare @colnm nvarchar(128)
declare @reftabnm nvarchar(128)
declare @refcolnm nvarchar(128)
declare @constnm nvarchar(128)

DECLARE @TempTable TABLE([Table Name] nvarchar(50),[Constraint Column Name] nvarchar(50),[Constraint Type] nvarchar(50),[Reference Table] nvarchar(50),[Reference Column Name] nvarchar(50),[Constraint Name] nvarchar(100))
select @flag=UPPER(@flag)
if @tabnm=''
begin
DECLARE cur_tabnm CURSOR FOR
select name from sysobjects where xtype='u'
OPEN cur_tabnm
FETCH NEXT FROM cur_tabnm into @tabnm
WHILE @@FETCH_STATUS = 0
BEGIN

begin
if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end
FETCH NEXT FROM cur_tabnm into @tabnm
END
CLOSE cur_tabnm
DEALLOCATE cur_tabnm
end
else if not exists(select * from sysobjects where name=@tabnm)
begin
Select 'Table Does Not Exist' as [Comments]
end
else
begin

if @flag='PK'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
end
else if @flag='FK'
begin
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
else if @flag='ALL'
begin
/*syscolumns.typestat=1 -> Not Null*/
/*syscolumns.xoffset=4 -> Primary Key*/
/*sysobjects.xtype='PK' -> Primary Key*/
if not exists(select SysObj1.name as [Table Name], SysCol.name as [Constraint Column Name], SysObj2.xtype as [Constraint Type] from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1)
begin
INSERT INTO @TempTable VALUES(@tabnm,'-','-NA-','','','')
end
else
begin
select @colnm=SysCol.name from sysobjects SysObj1,sysobjects SysObj2,syscolumns SysCol where SysObj1.name=@tabnm AND SysObj1.id=SysObj2.parent_obj and SysCol.id=SysObj1.id and SysObj2.xtype='PK' and SysCol.xoffset=4 and SysCol.typestat=1
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'PK','','','')
end
/*Foreign Key*/
select @constid=constid,@reftabid=rkeyid,@colid=fkey,@refcolid=rkey from sysforeignkeys where fkeyid=(select id from sysobjects where name=@tabnm)
select @reftabnm=name from sysobjects where id=@reftabid
select @constnm=name from sysobjects where id=@constid
select @colnm=name from syscolumns where id=(select id from sysobjects where name=@tabnm) and colid=@colid
select @refcolnm=name from syscolumns where id=@reftabid and colid=@refcolid
if not exists(select name from sysobjects where id=@reftabid)
begin
Select 'No Foreign Key Exist' as [Comments]
end
else
begin
INSERT INTO @TempTable VALUES(@tabnm,@colnm,'FK', @reftabnm, @refcolnm,@constnm)
end
end
end

select * from @TempTable
GO


Here is the execution of particular stored procedure in pubs database

exec sp_GetMasterConstraints '','pk'
exec sp_GetMasterConstraints '','fk'
exec sp_GetMasterConstraints '','all'
/*exec sp_GetMasterConstraints [table name – it will retrive all tables info if table name is not there],[Option ‘pk’ – Primary Key Alone, ‘fk’ Foreign Key Alone, ‘all’ - Both]*/