January 16, 2008 at 4:57 am
Hi,
I'm looking to see if there is a way of determining whether or not a foreign key has
Enforce Relationship For INSERTs and UPDATEs
set using T-SQL.
I can find out the Cascade Update/Delete Related Fields/Records using sp_FKeys (uses ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1 etc and I've tweaked the SP to include the For Replication flag in the same manner but I can't seem to determine the state of that specific option that you see in SSMS or Enterprise Manager. I can replicate the actual action with an "alter table" but I can't find where its "set". Ideally I don't want to use version specific T-SQL either!
Thanks in appreciation
Jamie
January 17, 2008 at 4:24 pm
Modified the original SP sp_Fkeys added two new fields, For_Replication and Constraint_Active. Hope this helps someone.
set nocount on
DECLARE @pktable_name sysname
SELECT @PKTable_Name='Account_Asset_Types'
DECLARE @pktable_id int DECLARE @pkfull_table_name nvarchar(257) /* 2*128 + 1 */
DECLARE @fktable_id int
DECLARE @fkfull_table_name nvarchar(257) /* 2*128 + 1 */
create table #fkeysall( rkeyid int NOT NULL, rkey1 int NOT NULL, rkey2 int NOT NULL,
rkey3 int NOT NULL, rkey4 int NOT NULL, rkey5 int NOT NULL, rkey6 int NOT NULL,
rkey7 int NOT NULL, rkey8 int NOT NULL, rkey9 int NOT NULL, rkey10 int NOT NULL,
rkey11 int NOT NULL, rkey12 int NOT NULL, rkey13 int NOT NULL, rkey14 int NOT NULL,
rkey15 int NOT NULL, rkey16 int NOT NULL, fkeyid int NOT NULL, fkey1 int NOT NULL,
fkey2 int NOT NULL, fkey3 int NOT NULL, fkey4 int NOT NULL, fkey5 int NOT NULL,
fkey6 int NOT NULL, fkey7 int NOT NULL, fkey8 int NOT NULL, fkey9 int NOT NULL, fkey10 int NOT NULL,
fkey11 int NOT NULL, fkey12 int NOT NULL, fkey13 int NOT NULL, fkey14 int NOT NULL, fkey15 int NOT NULL,
fkey16 int NOT NULL, constid int NOT NULL, name sysname collate database_default NOT NULL)
create table #fkeys( pktable_id int NOT NULL, pkcolid int NOT NULL, fktable_id int NOT NULL,
fkcolid int NOT NULL, KEY_SEQ smallint NOT NULL, fk_id int NOT NULL,
PK_NAME sysname collate database_default NOT NULL)
create table #fkeysout( PKTABLE_QUALIFIER sysname collate database_default NULL,
PKTABLE_OWNER sysname collate database_default NULL,
PKTABLE_NAME sysname collate database_default NOT NULL,
PKCOLUMN_NAME sysname collate database_default NOT NULL,
FKTABLE_QUALIFIER sysname collate database_default NULL,
FKTABLE_OWNER sysname collate database_default NULL,
FKTABLE_NAME sysname collate database_default NOT NULL,
FKCOLUMN_NAME sysname collate database_default NOT NULL,
KEY_SEQ smallint NOT NULL, UPDATE_RULE smallint NULL,
DELETE_RULE smallint NULL, FOR_REPLICATION smallint NULL,
CONSTRAINT_ACTIVE smallint NULL,
FK_NAME sysname collate database_default NULL,
PK_NAME sysname collate database_default NULL)
SELECT @pkfull_table_name = quotename(@pktable_name)
SELECT @pktable_id = object_id(@pkfull_table_name)
SELECT @fktable_id = object_id(@fkfull_table_name)
/* SQL Server supports upto 16 PK/FK relationships between 2 tables */
insert into #fkeysall
select
r.rkeyid,
r.rkey1, r.rkey2, r.rkey3, r.rkey4,
r.rkey5, r.rkey6, r.rkey7, r.rkey8,
r.rkey9, r.rkey10, r.rkey11, r.rkey12,
r.rkey13, r.rkey14, r.rkey15, r.rkey16,
r.fkeyid,
r.fkey1, r.fkey2, r.fkey3, r.fkey4,
r.fkey5, r.fkey6, r.fkey7, r.fkey8,
r.fkey9, r.fkey10, r.fkey11, r.fkey12,
r.fkey13, r.fkey14, r.fkey15, r.fkey16,
r.constid,
i.Name
From
sysreferences r, sysobjects o, sysindexes i
Where r.constid = o.ID
AND o.xtype = 'F'
AND r.rkeyindid = i.indid
AND r.rkeyid = i.id
AND r.rkeyid between isnull(@pktable_id, 0) and isnull(@pktable_id, 0x7fffffff)
AND r.fkeyid between isnull(@fktable_id, 0) and isnull(@fktable_id, 0x7fffffff)
insert into #fkeys
select rkeyid, rkey1, fkeyid, fkey1, 1, constid, name from #fkeysall
Union All
select rkeyid, rkey2, fkeyid, fkey2, 2, constid, name from #fkeysall
Union All
select rkeyid, rkey3, fkeyid, fkey3, 3, constid, name from #fkeysall
Union All
select rkeyid, rkey4, fkeyid, fkey4, 4, constid, name from #fkeysall
Union All
select rkeyid, rkey5, fkeyid, fkey5, 5, constid, name from #fkeysall
Union All
select rkeyid, rkey6, fkeyid, fkey6, 6, constid, name from #fkeysall
Union All
select rkeyid, rkey7, fkeyid, fkey7, 7, constid, name from #fkeysall
Union All
select rkeyid, rkey8, fkeyid, fkey8, 8, constid, name from #fkeysall
Union All
select rkeyid, rkey9, fkeyid, fkey9, 9, constid, name from #fkeysall
Union All
select rkeyid, rkey10, fkeyid, fkey10, 10, constid, name from #fkeysall
Union All
select rkeyid, rkey11, fkeyid, fkey11, 11, constid, name from #fkeysall
Union All
select rkeyid, rkey12, fkeyid, fkey12, 12, constid, name from #fkeysall
Union All
select rkeyid, rkey13, fkeyid, fkey13, 13, constid, name from #fkeysall
Union All
select rkeyid, rkey14, fkeyid, fkey14, 14, constid, name from #fkeysall
Union All
select rkeyid, rkey15, fkeyid, fkey15, 15, constid, name from #fkeysall
Union All
select rkeyid, rkey16, fkeyid, fkey16, 16, constid, name from #fkeysall
insert into #fkeysout
select
PKTABLE_QUALIFIER = convert(sysname,db_name()),
PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid)),
PKTABLE_NAME = convert(sysname,o1.name),
PKCOLUMN_NAME = convert(sysname,c1.name),
FKTABLE_QUALIFIER = convert(sysname,db_name()),
FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid)),
FKTABLE_NAME = convert(sysname,o2.name),
FKCOLUMN_NAME = convert(sysname,c2.name),
KEY_SEQ,
UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1) THEN
convert(smallint,0) ELSE convert(smallint,1) END,
DELETE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsDeleteCascade')=1) THEN
convert(smallint,0) ELSE convert(smallint,1) END,
FOR_REPLICATION = CASE WHEN (ObjectProperty(fk_id, 'CnstIsNotRepl')=0) THEN
convert(smallint,0) ELSE convert(smallint,1) END,
CONSTRAINT_ACTIVE = CASE (sc.status & 16384) WHEN 16384 THEN 1 ELSE 0 END,
FK_NAME = convert(sysname,OBJECT_NAME(fk_id)),
PK_NAME
from #fkeys f
inner join sysobjects o1 on o1.id = f.pktable_ID
inner join sysobjects o2 on o2.id = f.fktable_ID
inner join syscolumns c1 on c1.id = f.pktable_ID
and c1.colid = f.pkcolid
inner join syscolumns c2 on c2.id = f.fktable_ID
and c2.colid = f.fkcolid
left join sysconstraints sc on sc.constid = fk_id
and o1.id = c1.id
and sc.colid = c1.colid
select
PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME,
FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME,
KEY_SEQ, UPDATE_RULE, DELETE_RULE, FOR_REPLICATION, CONSTRAINT_ACTIVE,
FK_NAME , PK_NAME
from #fkeysout
order by 1,2,3,4,5,6,7
Drop table #fkeysall, #fkeys, #fkeysout
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply