How to See Recent Relationship Changes in SQL 2000 Database

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

You could find some useful tips here on Stack Overflow:

Is there an open source SQL Server DB compare tool?