Luciano Evaristo Guerche

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

Next friday is holyday in Sao Paulo state. I and my wife have had planned for a trip. Today, my boss informed me I'll have to work on friday, saturday and sunday. Just not mentioned I have worked last saturday and friday. And we are said slavery is something left in the past...

I am a MSDN Brazil subscriber. As so, regularly, I receive newsletters and the last ones informed about TechEd 2004 Brazil, happening 06/07/2004 through 08/07/2004. I forwarded all of them to my boss with no replies so far. Today she informed me our company is sponsoring her to go to TechEd 2004. That is a fair play, isn't it?

I generally create bit columns as NOT NULL DEFAULT dbo.BIT_NO, which in turn is a SQL server default which translates to 0. Today, I found out somebody on my team had created lots of bit fields on several tables as NULL with no default set. So I created the SQL statement below, to list these fields and make my life a bit easier. Comments are welcome. The fields I have to take care of is the ones with '*' on DNF column.

SELECT CASE WHEN dbo.syscomments.text IS NULL THEN '*' ELSE '' END AS [DNF], -- DNF = Default not found
       dbo.sysobjects.name AS tableName,
       dbo.syscolumns.name AS columnName,
       dbo.systypes.name AS typeName,
       dbo.syscolumns.isnullable, 
       REPLACE(dbo.syscomments.text, CHAR(13) + CHAR(10), '\n') AS [default bound with sp_bindefault]
FROM dbo.syscolumns
 INNER JOIN
     dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
 INNER JOIN
     dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
 LEFT OUTER JOIN
     dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id AND
                        OBJECTPROPERTY(dbo.syscolumns.cdefault, 'IsConstraint') = 0
WHERE (dbo.sysobjects.type = 'U') AND
      (dbo.systypes.name = 'bit') AND
      (dbo.syscolumns.isnullable = 0)
ORDER BY dbo.sysobjects.name,
         dbo.syscolumns.name

þ

I must give some of the Express Editions a try. Let's start downloading right now...

[Via Christa Carpentiere]
[Via Duncan Mackenzie]
[Via Benjamin Mitchell]
[Via Tim Sneath]
[Via Bill McCarthy]
[Via Robert Scoble]
[Via Chris Sells]
[Via Kent Sharkey]
[Via Kent Sharkey]
[Via Dan Fernandez]
[Via Shawn Wildermuth]

I have had problems syndicating some feeds lately. Can someone out there help me anyway? I send feedback to the webmasters, but no answer so far.

4GuysFromRolla.com Headlines. Could not read this feed due to invalid XML or the feed is in HTML. This may correct itself when the author updates the feed. The error was: O caractere '1', valor hexadecimal 0x31, não pode ser iniciado por um nome. Linha 20, posição 65..

Maxim V. Karpov
We were temporarily redirected to /simpleblog/Error.htm?aspxerrorpath=/simpleblog/BlogContent.asmx/GetRss but the link seems to be invalid.

The buzz has been circulating about the Imagine Cup finals in São Paulo, Brazil from July 2nd to July 8th. Jeff is set to go. Amy is set to go. Scoble is jealous.