April 27, 2010 at 9:33 am
Hi,
I've been looking into the database that our product is based on and trying to improve performance and generally tidy up. One area that I suspect is an issue is missing Foreign Keys. I'm trying to write a query to compare the column name of a primary key in one table to the column names of non-key columns in other tables. I realise that this will in no way give me a full list but it would be a start.
I've been playing with various system tables with little avail. I'm trying to get a resultresult set that consists of PKTableName, PKColumnName, TableName and PotentialFK. Has anyone got any experience of this?
Any suggestions would be great.
Thanks
Ally
April 27, 2010 at 10:50 am
ally this might get you started:
i made this script a long time ago for SQL 2000, so it does not take schemas into consideration;
it makes the following assumptions:
the PK of a table is the first column, and has a naming convention for a suffix, like %TBLKEY or %ID
a column that should be aforeign key has the PK column name in it; so CarsID exists in both the parent and child, but is missing an actual foreign key. it couldn't find stuff that was "CarsId" in the parent, but "CarsFK" in the child
you can just comment out the naming convention stuff if it does not apply to your data.
this just generates potential ALTER TABLE ADD ...foreign key statements, you'd of course have to review them.
hope this helps:
if object_id('tempdb..#FKFINDER') Is not null
drop table #FKFINDER
if object_id('tempdb..#CurrFKS') Is not null
drop table #CurrFKS
SELECT
CONVERT(VARCHAR(500),'') AS STATUS ,
SYSOBJECTS.NAME AS TBLNAME,
SYSOBJECTS.ID AS TBLID,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
--tables we know we can exclude
AND SYSOBJECTS.NAME NOT IN('CMCONT','CMMLADDR','CMADDR')
AND TYPE_NAME(SYSCOLUMNS.XTYPE)='INT'
AND SYSCOLUMNS.NAME LIKE '%TBLKEY' --or '%ID', depends on your schema
ORDER BY TBLNAME,COLNAME
SELECT
OBJECT_NAME(constid) AS CONSTRAINTNAME,
OBJECT_NAME(rkeyid) AS REFTABLE,
COL_NAME(rkeyid,rkey) AS REFCOLUMN,
OBJECT_NAME(fkeyid) AS FKTABLE,
COL_NAME(fkeyid,fkey) AS FKCOLUMN,
' ALTER TABLE ' + OBJECT_NAME(fkeyid)
+ ' ADD CONSTRAINT ' + OBJECT_NAME(constid)
+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)
+ ') REFERENCES ' + OBJECT_NAME(rkeyid)
+'(' + COL_NAME(rkeyid,rkey) + ')' AS FKSQL
Into #CurrFKS
from sysforeignkeys
--mark all the PK's
UPDATE #FKFINDER
SET STATUS = 'PK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.REFTABLE
AND #FKFINDER.COLNAME = #CURRFKS.REFCOLUMN
UPDATE #FKFINDER
SET STATUS = 'PK'
FROM(
SELECT OBJECT_NAME(ID) AS PKTABLE,
NAME as PKCOLUMN,
* FROM SYSCOLUMNS
WHERE SYSCOLUMNS.NAME LIKE '%TBLKEY' --or '%ID', depends on your schema
AND COLID = 1) X
WHERE #FKFINDER.TBLNAME = X.PKTABLE
AND #FKFINDER.COLNAME = X.PKCOLUMN
--mark all the curr FK's
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN
--fix specific to mail columns
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN
--here's a best guess as to what it might point to:
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND #FKFINDER.COLNAME = X.COLNAME
--fix against columns starting with 'MAIL'
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND REPLACE(#FKFINDER.COLNAME,'MAIL','') = X.COLNAME
--this is a list of all columns that end in "TBLKEY", which implies a FK link according to our schema, but does nto have a FK using the table/column.
--not definitive, but a good guess/good start need to examine carefully
SELECT *
FROM #FKFINDER
WHERE STATUS NOT IN('PK','FK','')
AND STATUS LIKE 'ALTER%'
--and status like '%GMACT%'
--and status not like '%OLD%'
ORDER BY TBLNAME,COLNAME
Lowell
April 28, 2010 at 6:37 am
On a side note, foreign keys are for data integrity. They have little effect on performance. It's indexes that you need to look at to really alter performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2010 at 10:07 am
Hi thanks for the replies. Schemas aren't really an issue as they're not in use, I will have a play with the script and see how I get on. Regarding performance, I can see from my vague comments that it may seem as though I suspected the missing keys could be affecting performance. Thanks for pointing that out, however this task falls under the general tidying up bracket I mentioned. I have a long list of indexes that the optimizer(sic) believes are missing and I have consolidated some of these into indexes but at present I don't have a baseline to measure against so I'm twiddling my thumbs.
Once again thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply