SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sysconstraints
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

svicky9
Posting Yak Master

United Kingdom
232 Posts

Posted - 09/01/2006 :  06:17:33  Show Profile  Visit svicky9's Homepage  Reply with Quote

The Table structure for sysconstraints as per book online

constid int Constraint number.
id int ID of the table that owns the constraint.
colid smallint ID of the column on which the constraint is defined, 0 if a table constraint.
spare1 tinyint Reserved.
status int Bitmap indicating the status.

Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.

actions int Reserved.
error int Reserved.

---
But when i go to Query Analyser and type
select * from sysconstraints

i get the Column status as
2067
2068
2069

So why is it not showing it as 1,2,3

is it something to do with the Bitmap???

Vic

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 09/01/2006 :  06:28:01  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Yes, it is that way because it stores combination of values as a bitmap. If you want to check individual status bit, you have to do it like this...

select 
	case when status & 1 = 1 then 'primary key ' else '' end + 
	case when status & 2 = 2 then 'unique key ' else '' end + 
        .....
from sysconstraints


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

svicky9
Posting Yak Master

United Kingdom
232 Posts

Posted - 09/01/2006 :  06:47:59  Show Profile  Visit svicky9's Homepage  Reply with Quote
Thanks Harsh

if i do status & 3 = 3 it should print it as Foreign key ...But its displaying as Primary
If the Column is the Foreign Key of some other table and the primary key of the same table how does sql server distinguish it
Morever the Bitmap values show correctly distinguishing the primary and Foreign Key columns

I did the test on the Sample Northwind Database


Vic

Vic
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/01/2006 :  07:23:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Maybe this binary explanation will help you

       2 1
       0 0 5 2 1
       4 2 1 5 2 6 3 1
       8 4 2 6 8 4 2 6 8 4 2 1

2067 - 1 0 0 0 0 0 0 1 0 0 1 1
2068 - 1 0 0 0 0 0 0 1 0 1 0 0
2069 - 1 0 0 0 0 0 0 1 0 1 0 1

       ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/01/2006 07:25:03
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/01/2006 :  07:37:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try this
SELECT		sc.constid ConstraintID,
		so.name TableName,
		CASE WHEN sc.colid = 0 THEN '' ELSE s.name END ColumnName,
		CASE
			WHEN sc.Status & 32 > 0 then 'Table-level'
			WHEN sc.Status & 16 > 0 then 'Column-level'
			WHEN sc.Status &  5 > 0 then 'DEFAULT'
			WHEN sc.Status &  4 > 0 then 'CHECK'
			WHEN sc.Status &  3 > 0 then 'FOREIGN KEY'
			WHEN sc.Status &  2 > 0 then 'UNIQUE KEY'
			WHEN sc.Status &  1 > 0 then 'PRIMARY KEY'
		END + ' 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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/01/2006 :  07:39:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by svicky9

if i do status & 3 = 3 it should print it as Foreign key ...But its displaying as Primary
When checking for 3, the two rightmost bits are set, bit 1 and bit 2. So you have to be careful in which order you test them.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

svicky9
Posting Yak Master

United Kingdom
232 Posts

Posted - 09/01/2006 :  07:50:49  Show Profile  Visit svicky9's Homepage  Reply with Quote
thanks Peso

But it shows only the Table level and Column level Constraints nothing else



Vic
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/01/2006 :  07:55:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Slightly revised
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


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

svicky9
Posting Yak Master

United Kingdom
232 Posts

Posted - 09/01/2006 :  08:43:59  Show Profile  Visit svicky9's Homepage  Reply with Quote
well thats worked ...thanks PESO

Vic
Go to Top of Page

hacene
Starting Member

2 Posts

Posted - 09/27/2012 :  07:02:52  Show Profile  Reply with Quote
so whene we have a foreign kay this script will show it as "forign key + unique key + primary key"
because filter for froreigne key is 3 : 1 1
filter for unique key is                2 : 1 0
filter for primary key is               1 : 0 1
Go to Top of Page

hacene
Starting Member

2 Posts

Posted - 09/27/2012 :  07:13:17  Show Profile  Reply with Quote
I mean that when we have a foreign key it could not be parimary or unique key
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000