SQL Server stores the full definition of certain T-SQL objects — Stored Procedures, Functions, Views, and Triggers — in their original form, as they are being created. In fact, the entire batch containing the CREATE statement is stored, which is quite likely why no other statements are allowed in a query batch that creates one of these types of objects.

These definitions can be accessed using the OBJECT_DEFINITION built-in function, or selecting from the sys.sql_modules system catalog view.

The following tests illustrate the information noted above. Please note that:

• comments, both before and after the body of the object, are stored along with the object’s definition
• when using CREATE, the word “CREATE” is stored in its original form
• when using ALTER, the word “alter” is replaced by the word “CREATE” in all upper-case
• when using CREATE OR ALTER (introduced in SQL Server 2016 SP1), the word “CREATE” is stored in its original form while the words “OR ALTER” are removed

USE [tempdb];
GO
/* comment */cReAtE PROCEDURE Test1A
AS
SELECT 1;
GO
GO
/*X*/CrEaTe or/*Y*/ALTer PROCEDURE Test1B
AS
BEGIN
SELECT 2;
END
/*Z*/GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Test1A'));
-- /* comment */  cReAtE PROCEDURE Test1A  AS  SELECT 1;  
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Test1B'));
-- /*X*/  CrEaTe /*Y*/ PROCEDURE Test1B  AS  BEGIN  SELECT 2;  END  /*Z*/  
GO
/* comment */ALteR PROCEDURE Test1A
AS
SELECT 3;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Test1A'));
-- /* comment */  CREATE PROCEDURE Test1A  AS  SELECT 3;  

Now that we see the basic concept, we can focus our attention on the fact that the object name is part of that definition. One behavior that has been documented is that when using the sp_rename system stored procedure to rename any of these four types of objects, the name of the object within the object’s definition is not updated; only the name column of sys.objects is updated.

The following test shows that after renaming the “Test1A” stored procedure to be “Test1ccc”, the system does indeed recognize the new object name, yet the old object name is still the one shown in the definition:

EXEC sp_rename N'Test1A', N'Test1ccc';
-- Caution: Changing any part of an object name could break scripts
--          and stored procedures.
SELECT OBJECT_DEFINITION(OBJECT_ID(N'Test1ccc'));
-- /* comment */  CREATE PROCEDURE Test1A  AS  SELECT 3;  

This behavior is noted in the Microsoft documentation for sp_rename:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.

Ok, so we have all been warned, at least when it comes to using sp_rename. But that is not the end of the story. There is, indeed, another way to change the object such that the definition does not reflect its current state. And that other way has to do with something missing from the examples shown thus far, something that wouldn’t be missing had I been following best-practices.

Starting in SQL Server 2005, the concept of Schemas was introduced. Schemas contain objects, and their name goes in place of what had been the owner’s name in the fully-qualified object’s name (ignoring, for the moment, the Server name):

SQL Server 2000 (and before): DatabaseName.OwnerName.ObjectName

SQL Server 2005 (and after): DatabaseName.SchemaName.ObjectName

Each User has a default Schema. When you do not include a Schema name when referencing an object, the default value, found in the default_schema_name column in sys.database_principals, is used. To avoid potentially differing behavior of queries between Users who might have different default Schema’s, it is best to always specify the Schema name for all Schema-bound objects.

The following example shows that the Schema name is included in the object’s definition when it is specified in the CREATE ... statement:

GO
CreAte PROCEDURE dbo.Test2
AS
SELECT 4;
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.Test2'));
-- CreAte PROCEDURE dbo.Test2  AS  SELECT 4;  

But now, let’s say that we already have, or are creating, a different Schema that we want this object to exist in. To move objects between Schemas without dropping and recreating them, the ALTER SCHEMA … TRANSFER command is used:

GO
CREATE SCHEMA [Other]; -- must be the only statement in batch
GO
ALTER SCHEMA [Other]
TRANSFER [dbo].[Test2];

If we check the definition again, using the old Schema name, we should get a NULL since the object isn’t in that Schema anymore:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.Test2'));
-- NULL

And sure enough, that behaved as expected.

If we check using the new Schema name, we should get the definition:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'Other.Test2'));
-- CreAte PROCEDURE dbo.Test2  AS  SELECT 4;  

And that, too, behaved as expected. BUT, look at the Schema name in the returned definition: it still shows “dbo” as the Schema instead of “Other”. Is there any mention of this behavior in the documentation for ALTER SCHEMA? Nope (well, at least not yet ;-).

Conclusion

While it is documented and known that sp_rename does not update the object name in the stored string definitions for Stored Procedures, Triggers, Functions, and Views, it is neither documented nor known that changing the Schema for one of these types of objects also leaves the definition unchanged (at least I couldn’t find any references to this behavior). But, now that we know of this behavior, here are some things to keep in mind:

1. If you script out object definitions using the OBJECT_DEFINITION built-in function and / or selecting from the sys.sql_modules system catalog view, then you have two enemies, not one: sp_rename and ALTER SCHEMA. It might be best to not script objects this way.
2. Scripting objects in SQL Server Management Studio (SSMS) changes the Schema name and object name to be the actual, current (and expected!) values, regardless of what is in the stored definition.
3. It is likely that the SMO .NET classes for scripting also make these corrections for you, but I haven’t tested them so cannot say for sure (but I would expect that they do because I believe that they are what SSMS is using)
4. The preferred method for moving these fours types of objects between Schemas (as well as the preferred method for renaming them) is to drop and recreate them. This will ensure that the full string definition matches the name and Schema values stored in sys.objects.
5. Moving objects of types other than the four types noted here is fine as they don’t have stored string definitions.

I will submit an update to the ALTER SCHEMA documentation to include a note regarding this behavior.

UPDATE 2018-01-10

The correction has been submitted on GitHub via Pull Request: Improve ALTER SCHEMA, sp_rename, and sys.sql_modules #335