Bug in INFORMATION_SCHEMA?

We just confirmed that if you change the length of a varchar in a table,
INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
for views that use that varchar. You have to drop and recreate the view for
the update to occur. This is a bug in my opinion especially since MS insits
you use the views and not the underlying sys tables. We use a C# class
generator that creates code based on those values and ran into this problem.
Anyone else noticed this?
</joel>That's not a bug -- the problem is that altering a table does not alter the
view that references the table. For instance, you can alter a table and
drop a column, and the view will not be affected -- you'll find out next
time you query it, though!
One way to keep problems like this from happening is to use the WITH
SCHEMABINDING option. This will disallow changes to the base table unless
you drop the view -- meaning that the view will have to be recreated after
you alter the table, and the INFORMATION_SCHEMA will therefore get updated.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur. This is a bug in my opinion especially since MS
insits
> you use the views and not the underlying sys tables. We use a C# class
> generator that creates code based on those values and ran into this
problem.
> Anyone else noticed this?
> </joel>
>|||When you change an underlying table, view metadata (in system tables) is not
automatically updated. You need to execute sp_refreshview or ALTER or
DROP/CREATE the view.
Note that this affects not only the data returned by the INFORMATION_SCHEMA
views but also the view itself. You will get old column definitions until
the metadata is refreshed. Illustrative script:
CREATE TABLE Table1(Col1 varchar(10))
GO
CREATE VIEW View1 AS SELECT Col1 FROM Table1
GO
EXEC sp_help 'View1' -- length 10
ALTER TABLE Table1 ALTER COLUMN Col1 varchar(20)
EXEC sp_help 'View1' -- length 10
EXEC sp_refreshview 'View1'
EXEC sp_help 'View1' -- length 20
GO
We follow a standard practice of executing sp_refreshview against all views
following schema changes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||That's not a bug in the INFORMATION_SCHEMA really. The metadata about the
columns that are in a view are stored with the view definition and are not
automatically updated when you change the underlying table(s). (This is a
one of the reasons why it is no good idea to use SELECT * in a view).
You don't have to drop and recreate the view for the metadata to be updated
though, you can achieve that with:
EXEC sp_refreshview '<view_name>'
Jacco Schalkwijk
SQL Server MVP
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> We just confirmed that if you change the length of a varchar in a table,
> INFORMATION_SCHEMA.CHARACTER_MAXIMUM_LENGTH does not automatically update
> for views that use that varchar. You have to drop and recreate the view
> for the update to occur. This is a bug in my opinion especially since MS
> insits you use the views and not the underlying sys tables. We use a C#
> class generator that creates code based on those values and ran into this
> problem.
> Anyone else noticed this?
> </joel>
>|||> for views that use that varchar. You have to drop and recreate the view
for
> the update to occur.
Are you changing the definition of the view, or the underlying table? If
you create your view with SCHEMABINDING, you won't be able to change the
underlying table, and this won't be an issue. Otherwise, plan to run
sp_refreshview on all views that reference the table in order to update the
metadata (this applies both to
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH and syscolumns.length).
Books Online documents this quite well, in fact:
"Refreshes the metadata for the specified view. Persistent metadata for a
view can become outdated because of changes to the underlying objects upon
which the view depends."
While it would be a nice enhancement for the metadata to truly reflect the
current state, I don't think Microsoft will consider it a bug because the
behavior is documented. I also think it would be quite expensive for SQL
Server to watch for ALTER TABLE events and then go and parse all the views
that reference it and make sure their metadata is updated also...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Wow, DBAs! :-)
I can certainly see in the case of dropping a column that SqlServer wouldn't
parse a View and try to figure out the program's intent, but a column length
is a column length.
If it walks like a bug...
</joel>
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
> That's not a bug -- the problem is that altering a table does not alter
> the
> view that references the table. For instance, you can alter a table and
> drop a column, and the view will not be affected -- you'll find out next
> time you query it, though!
> One way to keep problems like this from happening is to use the WITH
> SCHEMABINDING option. This will disallow changes to the base table unless
> you drop the view -- meaning that the view will have to be recreated after
> you alter the table, and the INFORMATION_SCHEMA will therefore get
> updated.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Joel" <joelycat@.hotmail.com> wrote in message
> news:%23uM$EeWKFHA.4092@.tk2msftngp13.phx.gbl...
> for
> insits
> problem.
>|||You found that response "Angry".
You haven't been in this newsgroup very long, have you?
Don't take your frustration out on people answering your questions just
because you don't like the answer.
If you don't like how SQL server handles this send your request to
Microsoft.
"Joel" <joelycat@.hotmail.com> wrote in message
news:%23GqmeCXKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Wow, DBAs! :-)
> I can certainly see in the case of dropping a column that SqlServer
> wouldn't parse a View and try to figure out the program's intent, but a
> column length is a column length.
> If it walks like a bug...
> </joel>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uWIIeoWKFHA.4028@.tk2msftngp13.phx.gbl...
>|||> Wow, DBAs! :-)
Who's ?

> If it walks like a bug...
Again, a bug to you is not a bug to everybody. This behavior is well-known
and well-documented.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oqfel%23XKFHA.3184@.TK2MSFTNGP09.phx.gbl...
> Who's ?
I'm that someone would imply that I am .
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--