0

enter image description here

I tracked down a bug in my system to this anomaly - at least it's an anomaly in my system of 15 catalogs with similar but unequal schemas.

What causes the [TABLE_NAME] in [INFORMATION_SCHEMA].[VIEWS] to be different than the value in [VIEW_DEFINITION]?

It makes me think I don't understand something about Views or System Tables in SQL Server... .

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Aaron Anodide
  • 16,906
  • 15
  • 62
  • 121

1 Answers1

3

If you have renamed the view, the name changes, but the definition doesn't.

You should do this as a DROP/CREATE or an ALTER script, not by right-clicking or using sp_rename.

This is actually expected behavior for all modules. Here is a quick test using a simple stored procedure:

CREATE PROCEDURE dbo.proc_foo
AS
    SELECT 1;
GO

-- rename it to proc_bar

EXEC sys.sp_rename N'dbo.proc_foo', N'proc_bar', N'OBJECT';
GO

-- check the definition from various sources

SELECT od = OBJECT_DEFINITION(OBJECT_ID(N'dbo.proc_bar')),
    info_s = (SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 
              WHERE ROUTINE_NAME = N'proc_bar' AND SCHEMA_NAME = N'dbo'),
    sql_m = (SELECT definition FROM sys.sql_modules 
             WHERE [object_id] = OBJECT_ID(N'dbo.proc_bar'));

Results:

od                             info_s                         sql_m
-----------------------------  -----------------------------  -----------------------------
CREATE PROCEDURE dbo.proc_foo  CREATE PROCEDURE dbo.proc_foo  CREATE PROCEDURE dbo.proc_foo
AS                             AS                             AS
    SELECT 1;                      SELECT 1;                      SELECT 1;

In any case, you shouldn't be using INFORMATION_SCHEMA anyway...

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • I'm sure you are correct about how it got into this state. `sp_refreshsqlmodule` got an error that looked like it tried to parse the same stale view definition that is in `INFORMATION_SCHEMA`. I tried your other approach but I didn't know where to ge the definition from. Using the object_id from sys.view in conjunction with the two methods of getting a view definition found at http://msdn.microsoft.com/en-us/library/ms345522.aspx#_FAQ35 yielded the stale definition as well. Finally I scripted an `ALTER VIEW` in SMSS and just ran it and happily that turned out to be my fix! – Aaron Anodide May 22 '12 at 17:04
  • The definition is found in [`sys.sql_modules`](http://msdn.microsoft.com/en-us/library/ms175081(SQL.105).aspx) which you can join to [`sys.objects`](http://msdn.microsoft.com/en-us/library/ms190324%28SQL.105%29.aspx) or [`sys.views`](http://msdn.microsoft.com/en-us/library/ms190334%28SQL.105%29.aspx) on `[object_id]`. You can also use the [`OBJECT_DEFINITION()`](http://msdn.microsoft.com/en-us/library/ms176090(SQL.105).aspx) metadata function. – Aaron Bertrand May 22 '12 at 17:10
  • Thanks for the updates. Just as some background, this catalog was created during my second month using Sql Server on the job and I hadn't yet caught on to the usefulness of the `SCRIPT AS` technique and I'm learning just now that SMSS calls sp_rename as opposed to generating the appropriate `ALTER` statement. – Aaron Anodide May 22 '12 at 17:11