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