| Author |
 Topic  |
|
|
svicky9
Posting Yak Master
United Kingdom
232 Posts |
 Posted - 09/01/2006 : 06:17:33
|
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
|
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" |
 |
|
|
svicky9
Posting Yak Master
United Kingdom
232 Posts |
Posted - 09/01/2006 : 06:47:59
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/01/2006 : 07:23:59
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/01/2006 : 07:37:39
|
Try thisSELECT 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/01/2006 : 07:39:42
|
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 |
|
|
|
svicky9
Posting Yak Master
United Kingdom
232 Posts |
Posted - 09/01/2006 : 07:50:49
|
thanks Peso
But it shows only the Table level and Column level Constraints nothing else
Vic |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/01/2006 : 07:55:36
|
Slightly revisedSELECT 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 |
|
|
|
svicky9
Posting Yak Master
United Kingdom
232 Posts |
Posted - 09/01/2006 : 08:43:59
|
well thats worked ...thanks PESO
Vic |
|
|
|
hacene
Starting Member
2 Posts |
Posted - 09/27/2012 : 07:02:52
|
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 |
|
|
|
hacene
Starting Member
2 Posts |
Posted - 09/27/2012 : 07:13:17
|
| I mean that when we have a foreign key it could not be parimary or unique key |
|
|
| |
Topic |
|
sysconstraints
