Listing ON DELETE CASCADE Constraints in MS SQL Server 2000

I needed to run my checks on ON DELETE CASCADE Constraints in MS SQL Server on SQL Server 2000, and needed to adjust the system table names and columns accordingly.

These are the queries adjusted for SQL Server 2000:

DECLARE @t NVARCHAR(128)
SET @t = 'MyTable'

SELECT p.name,
    objectproperty(fk.constid, 'CnstIsDeleteCascade') OnDelete,
    t.name
FROM dbo.sysforeignkeys fk
INNER JOIN dbo.sysobjects t ON fk.fkeyid = t.id
INNER JOIN dbo.sysobjects p ON fk.rkeyid = p.id
WHERE p.name = @t
ORDER BY 1, 3

SELECT DISTINCT p.name,
    objectproperty(fk.constid, 'CnstIsDeleteCascade') OnDelete,
    t.name,
    objectproperty(fk2.constid, 'CnstIsDeleteCascade') OnDelete,
    tt.name
FROM sysforeignkeys fk
INNER JOIN sysobjects t ON fk.fkeyid = t.id
INNER JOIN sysobjects p ON fk.rkeyid = p.id
INNER JOIN sysforeignkeys fk2 ON fk2.rkeyid = t.id
INNER JOIN sysobjects tt ON fk2.fkeyid = tt.id
WHERE p.name = @t
AND t.id <> p.id
ORDER BY 1, 3, 5

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: