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! 🙂