INFORMATION_SCHEMA query question: constraint columns

[Dave Sisk]

Hi Folks:

I'm a little new to SQLServer, so please pardon my ignorance!

I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list of
columns by constraint.

For instance, if Table1 has a unique key called Table1_UK01, I can find that
under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know the
columns in that UK constraint. I've tried
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this user
table doesn't seem to show up in either of those views.

Can anyone point me in the right direction? Any sample queries would be
tremendously appreciated. I'm going to be using this meta-data to
automatically generate quite a bundle of stored procs that do updates based
on finding rows via unique keys...

TIA,
Dave

[David Portas]

Unique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
constraint or an index? Use constraints and there shouldn't be a problem.
There is no physical difference between a unqiue constraint and a unique
index.

--
David Portas
SQL Server MVP
--

[Dave Sisk]

"David Portas" <RE****************************@acm.org> wrote in message news:<vp********************@giganews.com>...

Unique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
constraint or an index? Use constraints and there shouldn't be a problem.
There is no physical difference between a unqiue constraint and a unique
index.

Yes, these are declared as constraints, not just indexes.

I think I've figured out the problem, but I don't know how to fix it.
The user tables are all owned by a user we created called "dw". The
docs say that these views return info about objects the current user
has access to. If I select current_user, I get "dbo". I notice that
the information_schema.constraint_column_usage only returns info about
constraints where the table is owned by dbo.

When I connect, I'm connecting (in Query Analyzer, for instance) as
user dw, but if I immediately select current_user, it shows me "dbo".
I'd assume if I can connect as "dw" rather than "dbo", I'll actually
see the constraint_column_usage meta-data for tables owned by "dw"
rather than "dbo".

So, how do I "get connected" as the user "dw" rather than "dbo".
Logging in as SQLServer authenticated user "dw" obviously isn't doing
the trick. Is there some sort of ALTER statement to change my
current_user? (This is SQLServer 7.0, btw).

TIA!
Dave

[Erland Sommarskog]

Dave Sisk (ds***@nc.rr.com.0nospam0) writes:

I'm a little new to SQLServer, so please pardon my ignorance!

I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list
of columns by constraint.

For instance, if Table1 has a unique key called Table1_UK01, I can find
that under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to
know the columns in that UK constraint. I've tried
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this
user table doesn't seem to show up in either of those views.

Can anyone point me in the right direction? Any sample queries would be
tremendously appreciated. I'm going to be using this meta-data to
automatically generate quite a bundle of stored procs that do updates
based on finding rows via unique keys...

Rather than getting lost in the maze of the INFORMATION_SCHEMA views,
access the system tables directly. You will need to do that anyway if
you need information about indexes that are not constraints. Here is a
query:

SELECT i.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
AND ik.colid = c.colid
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND o.name = 'accountstats'
AND o.uid = USER_ID('dw')
ORDER BY i.name, ik.keyno

Gives you all indexes and their columns for this table. (It's possible
to constrain it to only unique constriaints, but I'm too lazy for that
now. Hint is that Unique constratins live in sysobjects too, and with
a parentobj = the object id for the table.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp