INFORMATION_SCH EMA 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_SCH EMA views for TABLES, COLUMNS, and
TABLE_CONSTRAIN TS. 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_SCH EMA.TABLE_CONST RAINTS. But I also need to know the
columns in that UK constraint. I've tried
INFORMATION_SCH EMA.CONSTRAINT_ COLUMN_USAGE and
INFORMATION_SCH EMA.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_COLU MN_USAGE and
KEY_COLUMN_USAG E 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******* *************@g iganews.com>...

Unique *constraints* will appear in both the CONSTRAINT_COLU MN_USAGE and
KEY_COLUMN_USAG E 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_sch ema.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_colu mn_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.co m.0nospam0) writes:

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

I've found the INFORMATION_SCH EMA views for TABLES, COLUMNS, and
TABLE_CONSTRAIN TS. 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_SCH EMA.TABLE_CONST RAINTS. But I also need to
know the columns in that UK constraint. I've tried
INFORMATION_SCH EMA.CONSTRAINT_ COLUMN_USAGE and
INFORMATION_SCH EMA.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_SCH EMA 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****@sommarsk og.se

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