aeri_q (10/17/2013)
Following procedure stores the value of each column in a parameter and then inserts in the table.CREATE PROCEDURE DBO.FKEYS_FOR_KEYTBL
@FKNAME VARCHAR(100) = NULL,
@SCHNAME VARCHAR(5) = NULL,
@FKFLDNAME VARCHAR(20) = NULL,
@FKTBLNAME VARCHAR(10) = NULL,
@PKFLDNAME VARCHAR(20) = NULL,
@PKTBLNAME VARCHAR(10) = NULL,
@KEYTBLNAME VARCHAR(10) = NULL
AS
BEGIN
SELECT @FKNAME= obj.name ,
@SCHNAME= sch.name ,
@FKTBLNAME= tab1.name ,
@FKFLDNAME= col1.name ,
@PKTBLNAME= tab2.name ,
@PKFLDNAME= col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where OBJECT_NAME(referenced_object_id) = @KEYTBLNAME
INSERT INTO SY.DB03F(FKNAME,SCHM,FKFLD,FKTBL,PKFLD,PKTBL)
VALUES(
@FKNAME,
@SCHNAME,
@FKFLDNAME,
@FKTBLNAME,
@PKFLDNAME,
@PKTBLNAME)
END
RETURN
GO
I think all you need here is just a simple INSERT... SELECT within the Stored Procedure with the @KEYTBLNAME as filter.
something along the lines of...
INSERT INTO SY.DB03F(FKNAME,SCHM,FKFLD,FKTBL,PKFLD,PKTBL)
SELECT obj.name ,
sch.name ,
tab1.name ,
col1.name ,
tab2.name ,
col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where OBJECT_NAME(referenced_object_id) = @KEYTBLNAME