tag:blogger.com,1999:blog-2886789781940464032.post1167608904356920078..comments2023-09-30T10:36:02.745+03:00Comments on Sql Server knowledge sharing blog: Finding missing relationshipShamas DBAhttp://www.blogger.com/profile/01894071527133572968noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-2886789781940464032.post-73792458768955558062009-05-20T11:42:45.425+03:002009-05-20T11:42:45.425+03:00Good link and another solution is this.
--Query t...Good link and another solution is this.<br /><br />--Query to find Constraints which are existing<br />select Tables1.Name PKTable, Columns1.Name as PKColumn, Tables2.Name FKTable, Columns2.Name FKColumn,<br /> case when FKs.constid is null then 0 else 1 end as HasFK<br /><br />from<br /> sysobjects Tables1 inner join <br /> (select id, count(*) as ColNo <br /> from sysindexkeys <br /> where indid = 1 <br /> group by id <br /> having count(*) = 1) PKColCount<br /> on PKColCount.id = Tables1.ID<br /> inner join syscolumns Columns1 on Columns1.id = Tables1.id and right(Columns1.Name, 2) = 'ID'-- not in ('Name', 'Status', 'UpdateDate', 'CreateDate')<br /> inner join sysindexkeys PKColumns on PKColumns.id = Tables1.id and PKColumns.indid = 1 and PKColumns.colid = Columns1.colid<br /> inner join syscolumns Columns2 on Columns1.Name = Columns2.Name and Columns1.id != Columns2.id<br /> inner join sysobjects Tables2 on Columns2.id = Tables2.id<br /> left join sysforeignkeys FKs on Columns1.colid = FKs.rkey and Columns2.colid = FKs.fkey<br /> and Columns1.id = FKs.rkeyid and Columns2.id = FKs.fkeyid<br />where Tables1.xtype = 'U' and Tables2.xtype = 'U' and FKs.constid is null and<br /> not<br /> (Tables1.Name like 'sys%' or Tables2.Name like 'sys%' or Columns1.Name like 'CommonName'<br /> --other combinations that are not relevant<br /> )<br /> <br /> <br /> --<br />-- SELECT f.name AS ForeignKey,<br />--OBJECT_NAME(f.parent_object_id) AS TableName,<br />--COL_NAME(fc.parent_object_id,<br />--fc.parent_column_id) AS ColumnName,<br />--OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,<br />--COL_NAME(fc.referenced_object_id,<br />--fc.referenced_column_id) AS ReferenceColumnName<br />--FROM sys.foreign_keys AS f<br />--INNER JOIN sys.foreign_key_columns AS fc<br />--ON f.OBJECT_ID = fc.constraint_object_id<br /><br /><br />--select * from syscolumns a where right(a.Name, 2) like 'ID'Shamas DBAhttps://www.blogger.com/profile/01894071527133572968noreply@blogger.com