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/mic...407b1b00d42b32
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/mic...407b1b00d42b32
> 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...
>
Showing posts with label return. Show all posts
Showing posts with label return. Show all posts
Monday, March 19, 2012
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:
a0407b1b00d42b32" target="_blank">http://groups.google.co.uk/group/mi...0407b1b00d42b32
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/mi...0407b1b00d42b32
> 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...
>
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:
a0407b1b00d42b32" target="_blank">http://groups.google.co.uk/group/mi...0407b1b00d42b32
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/mi...0407b1b00d42b32
> 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...
>
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'
>>
>
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'
>>
>
Monday, March 12, 2012
Key column is insufficient or incorrect. too many rows were affect
Hi,
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !
When I open table, return all rows using Enterprise Manager and I tried to
edit a column, it returned "Key column is insufficient or incorrect. too man
y
rows were affected by update"
This is a standalone table. No constraint. No formula.
Advice please. TIA !Any reasons to open it in EM?
Do you have any triggers on the table that you modifies to?
If you edit some row , click om another one and see if it worked
Do any modifications thru SP and not by EM
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too
> many
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !|||Has the table a primary key defined?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Desmond" <Desmond@.discussions.microsoft.com> wrote in message
news:51F80698-3DE1-49C7-88A0-093E583DF49F@.microsoft.com...
> Hi,
> When I open table, return all rows using Enterprise Manager and I tried to
> edit a column, it returned "Key column is insufficient or incorrect. too m
any
> rows were affected by update"
> This is a standalone table. No constraint. No formula.
> Advice please. TIA !
Subscribe to:
Posts (Atom)