December 20, 2011 at 2:34 pm
There's no doubt i've got some tunnel vision, and could use a bit of help trying to tweak my code to do this right.
I'm a serious script monkey, and now I'm trying to generate all foreign keys in a given database, and trying to take special care to handle those rare(for me anyway) foreign keys that comprise of multiple columns.
My current snippet is getting all columns that are involved in foreign keys , but is not grouping them by the constraint correctly...so if a table has two different foreign keys, my snippet is munging them into an incorrect group of columns so far.
I've played with it for quite a while, and as the title implies, can no longer see the foreign keys through the trees, as it were...i've lost perspective.
heres a simple example pair of tables that represents a multi column foreign key, so i can make sure it scripts both my singleton columns and multi column foreign keys as well.
a little help would be greatly appreciated.
CREATE TABLE [dbo].[VSTATE] (
[STATETBLKEY] int NOT NULL,
[INDEXTBLKEY] int NOT NULL,
[STATECODE] char(2) NOT NULL,
[STATENAME] varchar(50) NOT NULL,
[FIPS] char(3) NOT NULL,
CONSTRAINT [PK__TBSTATE__17cA421EC] PRIMARY KEY CLUSTERED ([STATETBLKEY]),
CONSTRAINT [STATECODEFIPSUNIQUE] UNIQUE NONCLUSTERED ([STATECODE],[FIPS]),
CONSTRAINT [STATECODEUNIQUE] UNIQUE NONCLUSTERED ([STATECODE]))
CREATE TABLE [dbo].[VCITY] (
[VCITYTBLKEY] int NULL,
[STATECODE] char(2) NOT NULL,
[FIPS] char(3) NOT NULL,
CONSTRAINT [FK__MULTICOL] FOREIGN KEY (STATECODE,FIPS) REFERENCES VSTATE(STATECODE,FIPS))
my script so far:
--script all foreign keys?
SELECT
'ALTER TABLE '
+ QUOTENAME(schema_name(conz.schema_id) )
+ '.'
+ QUOTENAME(OBJECT_NAME(conz.parent_object_id))
+ ' ADD CONSTRAINT '
+ QUOTENAME(conz.name)
+ ' FOREIGN KEY ('
+ ChildCollection.ChildColumns
+ ') REFERENCES '
+ QUOTENAME(SCHEMA_NAME(conz.schema_id))
+ '.'
+ QUOTENAME(OBJECT_NAME(conz.referenced_object_id))
+ ' (' + ParentCollection.ParentColumns
+ ') '
+ ';' AS Command
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
-- INNER JOIN #MyAffectedTables tabz
-- ON conz.parent_object_id = tabz.object_id
-- AND tabz.column_id = colz.parent_column_id
INNER JOIN (--gets my child tables column names
SELECT
name,
ChildColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.parent_object_id = REFZ.object_id
AND fkcolz.parent_column_id = REFZ.column_id
WHERE fkcolz.parent_object_id = conz.parent_object_id
ORDER BY
fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
AND conz.key_index_id = colz.constraint_column_id
GROUP BY
conz.name,
conz.parent_object_id--- without GROUP BY multiple rows are returned
) ChildCollection
ON conz.name = ChildCollection.name
INNER JOIN (--gets the parent tables column names for the FK reference
SELECT
name,
ParentColumns = STUFF((SELECT
DISTINCT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.referenced_object_id = REFZ.object_id
AND fkcolz.referenced_column_id = REFZ.column_id
WHERE fkcolz.referenced_object_id = conz.referenced_object_id
-- ORDER BY fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
-- AND colz.parent_column_id
GROUP BY
conz.name,
conz.referenced_object_id--- without GROUP BY multiple rows are returned
) ParentCollection
ON conz.name = ParentCollection.name
Lowell
December 21, 2011 at 3:53 am
This is very strange!!
I think the problem you are having is with the code to fetch the child columns (which are actually parent columns as far as the FK is concerned!!)
Run this part of the code in isolation, and you will see you actually get nothing returned because of this...
AND conz.key_index_id = colz.constraint_column_id (key_index_id should not be used here)
--gets my child tables column names
SELECT
name,
ChildColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.parent_object_id = REFZ.object_id
AND fkcolz.parent_column_id = REFZ.column_id
WHERE fkcolz.parent_object_id = conz.parent_object_id
ORDER BY
fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
AND conz.key_index_id = colz.constraint_column_id
GROUP BY
conz.name,
conz.parent_object_id--- without GROUP BY multiple rows are returned
So, let's remove the that part of the constraint, and we appear to get the correct result
SELECT
name,
ChildColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.parent_object_id = REFZ.object_id
AND fkcolz.parent_column_id = REFZ.column_id
WHERE fkcolz.parent_object_id = conz.parent_object_id
ORDER BY
fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
GROUP BY
conz.name,
conz.parent_object_id--- without GROUP BY multiple rows are returned
But if you add further constraints on the same table, we get incorrect results.
There is obviously a join condition missing as you are getting a cartesian product of all columns involved in any FK constraint involving that table.
Use these tables to demonstrate
CREATE TABLE TABLEA(col1 int, col2 int, col3 int, col4 int
CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (col1, col2),
CONSTRAINT UK_TABLEA UNIQUE NONCLUSTERED (col3, col4))
CREATE TABLE TABLEB(col1 int, col2 int, col3 int, col4 int ,
FOREIGN KEY (col1, col2) REFERENCES TABLEA(col1, col2),
FOREIGN KEY (col3, col4) REFERENCES TABLEA(col3, col4) )
Run the previous bit of code again and you should see the issue.
This is where it gets interesting... in the inner select, where does
"fkcolz.parent_object_id = conz.parent_object_id" think it is binding to?
If you try to add constraint_object_id to the where clause, you get an aggregate error!!
SELECT
name,
ChildColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.parent_object_id = REFZ.object_id
AND fkcolz.parent_column_id = REFZ.column_id
WHERE fkcolz.parent_object_id = conz.parent_object_id
AND fkcolz.constraint_object_id = conz.object_id
ORDER BY
fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
GROUP BY
conz.name,
conz.parent_object_id--- without GROUP BY multiple rows are returned
December 21, 2011 at 5:51 am
Thanks Ian;
your pointers and a fresh look at it thismorning got me where i wanted to go.
for reference, this modified verison seems to do exactly what I was trying to do:
--script all foreign keys?
SELECT
'ALTER TABLE '
+ QUOTENAME(schema_name(conz.schema_id) )
+ '.'
+ QUOTENAME(OBJECT_NAME(conz.parent_object_id))
+ ' ADD CONSTRAINT '
+ QUOTENAME(conz.name)
+ ' FOREIGN KEY ('
+ ChildCollection.ChildColumns
+ ') REFERENCES '
+ QUOTENAME(SCHEMA_NAME(conz.schema_id))
+ '.'
+ QUOTENAME(OBJECT_NAME(conz.referenced_object_id))
+ ' (' + ParentCollection.ParentColumns
+ ') '
+ ';' AS Command
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
-- INNER JOIN #MyAffectedTables tabz
-- ON conz.parent_object_id = tabz.object_id
-- AND tabz.column_id = colz.parent_column_id
INNER JOIN (--gets my child tables column names
SELECT
name,
ChildColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.parent_object_id = REFZ.object_id
AND fkcolz.parent_column_id = REFZ.column_id
WHERE fkcolz.parent_object_id = conz.parent_object_id
AND fkcolz.constraint_object_id = conz.object_id
ORDER BY
fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
GROUP BY
conz.name,
conz.parent_object_id,--- without GROUP BY multiple rows are returned
conz.object_id
) ChildCollection
ON conz.name = ChildCollection.name
INNER JOIN (--gets the parent tables column names for the FK reference
SELECT
name,
ParentColumns = STUFF((SELECT
',' + REFZ.name
FROM sys.foreign_key_columns fkcolz
INNER JOIN sys.columns REFZ
ON fkcolz.referenced_object_id = REFZ.object_id
AND fkcolz.referenced_column_id = REFZ.column_id
WHERE fkcolz.referenced_object_id = conz.referenced_object_id
AND fkcolz.constraint_object_id = conz.object_id
ORDER BY fkcolz.constraint_column_id
FOR XML PATH('')), 1, 1, '')
FROM sys.foreign_keys conz
INNER JOIN sys.foreign_key_columns colz
ON conz.object_id = colz.constraint_object_id
-- AND colz.parent_column_id
GROUP BY
conz.name,
conz.referenced_object_id,--- without GROUP BY multiple rows are returned
conz.object_id
) ParentCollection
ON conz.name = ParentCollection.name
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply