3

I have a SQL Server 2000 DB with a lot of PK/FK relationships changes in a specific time frame. Is there any way I can retrieve all the relationship changes or additions to a Database?

I tried this query which returns all the ForeignKeys.

SELECT f.constid, OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.rkeyid) AS 'PKTable', c2.[name] AS 'PKColumnName'
FROM sysforeignkeys f
INNER JOIN syscolumns c1
ON f.fkeyid = c1.[id]
AND f.fkey = c1.colid
INNER JOIN syscolumns c2
ON f.rkeyid = c2.[id]
AND f.rkey = c2.colid
ORDER BY constid
GO

I was hoping that the constid field would be sequential so I could just look for anything that was done after a specific constid. However that is not the case, it doesn't seem to write the constid in any sequential order.

Adam
  • 1,561
  • 2
  • 15
  • 25

2 Answers2

3

No, not unless you have a replica somewhere and a tool like Redgate's Compare.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1

Unless you do some special logging, with triggers so that when the relationships are created, changed, or deleted, there's no automated way of getting a list of recent relationship changes.

Kibbee
  • 65,369
  • 27
  • 142
  • 182