Update of IINFORMATION_SCHEMA.KEY_COLUMN_USAGE Sql CE

Question

How do you ensure INFORMATION_SCHEMA.KEY_COLUMN_USAGE is updated after you have added/created a new index. ie:

CREATE UNIQUE INDEX [UK_Common.UserConnection]
   ON [Common.UserConnection] ([SessionId] ASC,[UserId] ASC);
GO

I have to databases, one is transformed/updated from an old version (V1) to a new version (V10) the other is a fresh new (V10) database. When I compare the databases everything matches (this includes PK, FK, Indexes etc) except:

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

I can see that my newly created uniq index ([UK_Common.UserConnection] SessionId and UserId) is missing in the transformed database. The transformed database counts to less rows in the "key_column_usage" table compared to the new (V10) database.

If I extract "Create sql table script" from any sql ce client application the script for the transformed/updated and new (V10) script is identical.

I have tried to include an insert into statement in the transformer:

    INSERT INTO [INFORMATION_SCHEMA.KEY_COLUMN_USAGE]
            (COL0,COL1,COL2,COL3)
    VALUES ('value0','value1','value2',value3)

    GO

Result: Data cannot be added to a system table or a schema information view. [ Name of read-only table = @@INFORMATION_SCHEMA.KEY_COLUMN_USAGE ]

I have also tried to code this inn .net where the select query (rot to count key error) is located.

("SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE")

Result: Data cannot be added to a system table or a schema information view. [ Name of read-only table = @@INFORMATION_SCHEMA.KEY_COLUMN_USAGE ]

Question is related to a failing unit test (mstest) in an asp.net project: Assert.IsTrue failed. Count of key column usages is different.

How do you update: INFORMATION_SCHEMA.KEY_COLUMN_USAGE after editing/change of INDEX?

.NET 4.0 SQL CE 4.0 VS2010

That's handled by the dbms itself. – jarlh Oct 02 '15 at 12:14 — jarlh, Oct 02 '15 at 12:14

Shadow · Answer 1 · 2015-10-02T12:36:47.173

2

You cannot update this table (view), it will be updated by ms sql ce instance based on the use of the key. It's usually not such a good idea to modify the information_schema directly in any of the RDBMs. In most cases users are not allowed to do that anyway.

Edit: I do not think that differences in information_schema should be used as an exception criterion in testing.

edited Oct 02 '15 at 12:36

answered Oct 02 '15 at 12:18

Shadow

33,525
10
51
64

I agree, this is probably too deep for test purposes, anyhow when I drop the rows in the table and update with some new "demo" rows the information schema is not updated. I suspect that the information schema only can be "edited" up on creation of the DB? As result of inserting and deleting rows in the table now (after updating index) want effect the information schema? – Lars Oct 02 '15 at 12:58

Update of IINFORMATION_SCHEMA.KEY_COLUMN_USAGE Sql CE

1 Answers1