status in sysconstraints

anushahanna
anushahanna used Ask the Experts™
on
When I do
*select distinct status  from sysconstraints

I get 4 digit and 5 digit numbers. what are they?

http://msdn.microsoft.com/en-us/library/aa260399%28SQL.80%29.aspx
suggest single digit values...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008
Commented:
Looking..

in the mean time, you can use:  select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Awarded 2008
Awarded 2008
Commented:
SELECT            sc.constid ConstraintID,
            so.name TableName,
            CASE WHEN sc.colid = 0 THEN '' ELSE s.name END ColumnName,
            SUBSTRING(
                        CASE WHEN sc.Status & 32 = 32 THEN ', Table-level' ELSE '' END +
                        CASE WHEN sc.Status & 16 = 16 then ', Column-level' ELSE '' END +
                        CASE WHEN sc.Status &  5 = 5 then ', DEFAULT' ELSE '' END +
                        CASE WHEN sc.Status &  4 = 4 then ', CHECK' ELSE '' END +
                        CASE WHEN sc.Status &  3 = 3 then ', FOREIGN KEY' ELSE '' END +
                        CASE WHEN sc.Status &  2 = 2 then ', UNIQUE KEY' ELSE '' END +
                        CASE WHEN sc.Status &  1 = 1 then ', PRIMARY KEY' ELSE '' END,
                   3, 100) + ' constraint'
FROM            sysconstraints sc
INNER JOIN      sysobjects so ON so.id = sc.id AND so.xtype = 'u'
LEFT JOIN      syscolumns s ON s.colid = sc.colid AND so.id = s.id

Author

Commented:
Thanks Genius!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial