ALTER SCHEMA does not reflect changes into the sys.sql_modules catalog view (definition)

I have recently found a strange behavior related to a comfortable DDL command, the ALTER SCHEMA statement. That happened while I was trying to move a stored procedure from a schema to another one. Both stored procedure and function definitions are stored on a catalog object which is accessible by the sys.sql_modules catalog view (column “definition”). After executing the ALTER SCHEMA statement the definition field is not updated.

We’re speaking about both SQL Server 2012 on premise and SQL Azure databases. My current SQL Server 2012 version/edition is:

Let’s see an example.. We have to create some objects:

• – a schema
• – a table
• – a stored procedure

USE tempdb;
GO
-- creates a new schema
CREATE SCHEMA Foo;
GO
-- creates a new table  under Foo schema
CREATE TABLE Foo.Data
(
    IDFoo int NOT NULL
  , FooDescription varchar(100) NOT NULL
  , CONSTRAINT PK_FooData PRIMARY KEY CLUSTERED
  (
      IDFoo
  )
);
GO

-- creates stored procedure 
CREATE PROCEDURE Foo.proc_Foo_GetData
@IDFoo int
AS
BEGIN
      SELECT
              IDFoo = @IDFoo
            , FooDescription
      FROM
            Foo.Data
      WHERE
            IDFoo = @IDFoo; 
END;
GO

Let’s see the current stored procedure definition:

-- gets the sp definition
SELECT
        SpName = 'Foo.proc_Foo_GetData'
      , [definition]
FROM
      sys.sql_modules
WHERE
      object_id IN (OBJECT_ID('Foo.proc_Foo_GetData'));
GO

As we can see, the schema name is synced with the definition field data.

Now, let’s create a new schema, which will be the destination of the stored procedure transfer and the also the ALTER SCHEMA statement:

-- new schema
CREATE SCHEMA Valid;
GO

-- transfer stored procedure
ALTER SCHEMA Valid TRANSFER Foo.proc_Foo_GetData;
GO

Let’s check for the definition field again:

-- gets the sp definition again
SELECT
        SpName = 'Valid.proc_Foo_GetData'
      , [definition]
FROM
      sys.sql_modules
WHERE
      object_id IN (OBJECT_ID('Valid.proc_Foo_GetData'));
GO

As we can see, the schema of the new stored procedure is not the same of the one stored on the definition field.

I found this issue while searching via Red-Gate SQL Search. After a deeper inspection on the search query executed by the third party tool, I could understand that the problem was related to sql server metadata.

A workaround is to re-issue the ALTER PROCEDURE statement.

There’s also a similar issue described here on Connect, closed as “won’t fix”.

You can find my complete sample script, here.

Stay tuned! 🙂