Search code examples
sql-server-2008-r2system-tables

How does view name get out of sync with view definition?

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... .

Solution

  • 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...