KEY_COLUMN_USAGE bug

Is it on purpose or is it a bug that the view
INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
"dbo" schema?
I've checked the view code and came accross this selection criteria
...
WHERE
...
col.name = index_col(t_obj.name, i.indid, v.number) AND
...
and I would guess it should be
col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
v.number) AND
instead
is this bug on purpose or am I wrong?
it's kind of hard to get pk schema information when the schema isn't "dbo" -
what would the sql server experts solution be in this case?
regards
Chris
...
WHERE
...
SELECT
...
UNION
SELECT
db_name() AS CONSTRAINT_CATALOG,
user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
i.name AS CONSTRAINT_NAME,
db_name() AS TABLE_CATALOG,
user_name(t_obj.uid) AS TABLE_SCHEMA,
t_obj.name AS TABLE_NAME,
col.name AS COLUMN_NAME,
v.number AS ORDINAL_POSITION
FROM
sysobjects c_obj, sysobjects t_obj, syscolumns col,
master.dbo.spt_values v, sysindexes i
WHERE
permissions(t_obj.id) != 0 AND
c_obj.xtype IN ('UQ', 'PK') AND
t_obj.id = c_obj.parent_obj AND
t_obj.xtype = 'U' AND
t_obj.id = col.id AND
--**
col.name = index_col(t_obj.name, i.indid, v.number) AND
--**
t_obj.id = i.id AND
c_obj.name = i.name AND
v.number > 0 AND
v.number <= i.keycnt AND
v.type = 'P'It's a bug, and it was discussed earlier in the following thread:
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
I haven't checked if it has actually been fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"christian kuendig" <xxxx> wrote in message
news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Is it on purpose or is it a bug that the view
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
> "dbo" schema?
> I've checked the view code and came accross this selection criteria
> ...
> WHERE
> ...
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> ...
> and I would guess it should be
> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
> v.number) AND
> instead
> is this bug on purpose or am I wrong?
> it's kind of hard to get pk schema information when the schema isn't
> "dbo" - what would the sql server experts solution be in this case?
> regards
> Chris
> ...
> WHERE
> ...
> SELECT
> ...
> UNION
> SELECT
> db_name() AS CONSTRAINT_CATALOG,
> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
> i.name AS CONSTRAINT_NAME,
> db_name() AS TABLE_CATALOG,
> user_name(t_obj.uid) AS TABLE_SCHEMA,
> t_obj.name AS TABLE_NAME,
> col.name AS COLUMN_NAME,
> v.number AS ORDINAL_POSITION
> FROM
> sysobjects c_obj, sysobjects t_obj, syscolumns col,
> master.dbo.spt_values v, sysindexes i
> WHERE
> permissions(t_obj.id) != 0 AND
> c_obj.xtype IN ('UQ', 'PK') AND
> t_obj.id = c_obj.parent_obj AND
> t_obj.xtype = 'U' AND
> t_obj.id = col.id AND
> --**
> col.name = index_col(t_obj.name, i.indid, v.number) AND
> --**
> t_obj.id = i.id AND
> c_obj.name = i.name AND
> v.number > 0 AND
> v.number <= i.keycnt AND
> v.type = 'P'
>|||Hi Jacco
It seems to be the same in SP4.
John
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:Ooj3SEUWFHA.2984@.tk2msftngp13.phx.gbl...
> It's a bug, and it was discussed earlier in the following thread:
> http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/77f82a458be4b9d9/a0407b1b00d42b32?q=schalkwijk+key_column_usage&rnum=1#a0407b1b00d42b32
> I haven't checked if it has actually been fixed in SP4.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "christian kuendig" <xxxx> wrote in message
> news:OVHcnzSWFHA.3188@.TK2MSFTNGP09.phx.gbl...
>> Is it on purpose or is it a bug that the view
>> INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't return PK columns outside the
>> "dbo" schema?
>> I've checked the view code and came accross this selection criteria
>> ...
>> WHERE
>> ...
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> ...
>> and I would guess it should be
>> col.name = index_col(user_name(c_obj.uid) + '.' + t_obj.name, i.indid,
>> v.number) AND
>> instead
>> is this bug on purpose or am I wrong?
>> it's kind of hard to get pk schema information when the schema isn't
>> "dbo" - what would the sql server experts solution be in this case?
>> regards
>> Chris
>> ...
>> WHERE
>> ...
>> SELECT
>> ...
>> UNION
>> SELECT
>> db_name() AS CONSTRAINT_CATALOG,
>> user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
>> i.name AS CONSTRAINT_NAME,
>> db_name() AS TABLE_CATALOG,
>> user_name(t_obj.uid) AS TABLE_SCHEMA,
>> t_obj.name AS TABLE_NAME,
>> col.name AS COLUMN_NAME,
>> v.number AS ORDINAL_POSITION
>> FROM
>> sysobjects c_obj, sysobjects t_obj, syscolumns col,
>> master.dbo.spt_values v, sysindexes i
>> WHERE
>> permissions(t_obj.id) != 0 AND
>> c_obj.xtype IN ('UQ', 'PK') AND
>> t_obj.id = c_obj.parent_obj AND
>> t_obj.xtype = 'U' AND
>> t_obj.id = col.id AND
>> --**
>> col.name = index_col(t_obj.name, i.indid, v.number) AND
>> --**
>> t_obj.id = i.id AND
>> c_obj.name = i.name AND
>> v.number > 0 AND
>> v.number <= i.keycnt AND
>> v.type = 'P'
>>
>