DA, aka Data Administrators + ERWin = MESS
Last week, I found out DA team, using ERWin to synchronize models and databases, had messed up with some foreign keys on a database I architected for an application I am developing and the FKs were missing. Then the question "How to find out which FKs are missing on a database made of about 200 tables?" popped up.
As both parent colum name and child column name have the same name on my database and PK indexes are all named like 'XPK_%', I created the script below to help me out. Take a look at it and let your comments.
/*
DROP TABLE #constraints
*/
-- list all constraints on database
SELECT dbo.sysobjects.parent_obj AS tableid,
'PK' AS constraintType,
dbo.sysobjects.name AS constraintName,
dbo.syscolumns.name AS keyColumn,
dbo.sysindexkeys.keyno
INTO #constraints
FROM dbo.sysobjects
INNER JOIN
dbo.sysindexes ON dbo.sysobjects.name = dbo.sysindexes.name
INNER JOIN
dbo.sysindexkeys ON dbo.sysindexes.indid = dbo.sysindexkeys.indid AND
dbo.sysindexes.id = dbo.sysindexkeys.id
INNER JOIN
dbo.syscolumns ON dbo.sysindexkeys.colid = dbo.syscolumns.colid AND
dbo.sysobjects.parent_obj = dbo.syscolumns.id
WHERE dbo.sysobjects.xtype = 'PK'
UNION ALL
SELECT sysindexes.id AS tableid,
'AK' AS constraintType,
sysindexes.name AS constraintName,
syscolumns.name AS keyColumn,
sysindexkeys.keyno
FROM sysindexes
INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id AND
sysindexes.indid = sysindexkeys.indid
INNER JOIN
syscolumns ON sysindexkeys.id = syscolumns.id AND
sysindexkeys.colid = syscolumns.colid
INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysindexes.status & 2) <> 0 AND
sysindexes.name NOT LIKE 'XPK_%'
UNION ALL
SELECT sysobjects.parent_obj AS tableid,
'FK' AS constraintType,
sysobjects.name AS constraintName,
syscolumns.name AS keyColumn,
sysforeignkeys.keyno
FROM sysobjects
INNER JOIN
sysforeignkeys ON sysobjects.id = sysforeignkeys.constid
INNER JOIN
syscolumns ON sysforeignkeys.fkeyid = syscolumns.id AND
sysforeignkeys.fkey = syscolumns.colid
INNER JOIN
syscolumns AS syscolumnsParent ON sysforeignkeys.rkey = syscolumnsParent.colid AND
sysforeignkeys.rkeyid = syscolumnsParent.id
INNER JOIN
sysobjects AS sysobjectsParent ON syscolumnsParent.id = sysobjectsParent.id
WHERE sysobjects.xtype = 'F'
/*
DROP TABLE #duplicateKeyColumns
*/
-- Since both parent and child column names are the same, duplicates which are PK/AK
-- on some table might be the starting point of search
SELECT sc.name AS columnName,
COUNT(*) AS score
INTO #duplicateKeyColumns
FROM syscolumns sc
INNER JOIN
sysobjects so ON sc.id = so.id
WHERE so.type = 'U' AND
so.name NOT IN ('exclude list comes here') AND
EXISTS(
SELECT #constraints.keyColumn FROM #constraints
WHERE #constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('PK', 'AK')
)
GROUP BY sc.name
HAVING COUNT(*) > 1
ORDER BY score DESC
/*
DROP TABLE #duplicateKeyColumnsRelatedTables
*/
SELECT sc.name AS columnName,
so.name AS tableName,
CASE WHEN EXISTS(
SELECT #constraints.keyColumn
FROM #constraints
WHERE #constraints.tableid = so.id AND
#constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('PK')
)
THEN '*' ELSE NULL END AS [PK],
CASE WHEN EXISTS(
SELECT #constraints.keyColumn
FROM #constraints
WHERE #constraints.tableid = so.id AND
#constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('AK')
)
THEN '*' ELSE NULL END AS [AK],
CASE WHEN EXISTS(
SELECT #constraints.keyColumn
FROM #constraints
WHERE #constraints.tableid = so.id AND
#constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('FK')
)
THEN '*' ELSE NULL END AS [FK]
INTO #duplicateKeyColumnsRelatedTables
FROM syscolumns sc
INNER JOIN
sysobjects so ON sc.id = so.id
INNER JOIN
#duplicateKeyColumns ON sc.name = #duplicateKeyColumns.columnName
WHERE so.type = 'U' AND
so.name NOT IN ('exclude list comes here') AND
(
EXISTS(
SELECT #constraints.keyColumn
FROM #constraints
WHERE #constraints.tableid = so.id AND
#constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('PK', 'AK')
) OR
NOT EXISTS(
SELECT #constraints.keyColumn
FROM #constraints
WHERE #constraints.tableid = so.id AND
#constraints.keyColumn = sc.name AND
#constraints.constraintType IN ('FK')
)
)
ORDER BY #duplicateKeyColumns.score DESC,
#duplicateKeyColumns.columnName,
so.name
-- deletes all duplicate key columns which does not have table(s) missing any FK
DELETE #duplicateKeyColumnsRelatedTables
WHERE EXISTS(
SELECT COUNT(*)
FROM #duplicateKeyColumnsRelatedTables AS duplicateKeyColumnsRelatedTablesSQ
WHERE duplicateKeyColumnsRelatedTablesSQ.columnName = #duplicateKeyColumnsRelatedTables.columnName AND
duplicateKeyColumnsRelatedTablesSQ.[FK] IS NULL
HAVING COUNT(*) <= 1
)
-- for each column listed, one of the related tables, with PK containing the column name
-- on its list, is the root of information. When you find the root (parentTable), all the
-- other tables without FKs containing the column are the the tables missing FKs on them
SELECT *
FROM #duplicateKeyColumnsRelatedTables
WHERE [FK] IS NULL