sys.sql_modules

Added link to OBJECT_DEFINITION page in the "Description" column for definition. Execute the following to see that OBJECT_DEFINITION is the source for the definition column:

EXEC sp_helptext N'sys.sql_modules';

But even if it wasn't the source, it still outputs the same value.

ALTER SCHEMA and sp_rename

1. Added reference to OBJECT_DEFINITION function page for sp_rename.
2. Changed "schema-contained" to be "schema-scoped" to be consistent with OBJECT_NAME and OBJECT_SCHEMA_NAME documentation (and most likely other pages)
3. Copied two paragraphs of Remarks from sp_rename documentation and adjusted to pertain to this context
4. Removed "or view" from SSMS instructions since first new Remarks paragraph recommends against using ALTER SCHEMA on views 😉
5. Fixed IMPERSONATION permission (which doesn't exist) to be the actual IMPERSONATE permission
6. Please see related blog post containing explanation and example code / proof:
   Stored Procedure / Function / View / Trigger Definitions Can Be Wrong, Even If sp_rename Was Never Used

ALTER SCHEMA

Relating to the advice about the need to manually update referenced objects that have been transferred / moved, please execute the following:

USE [tempdb];

GO
CREATE SCHEMA [TransferTest];
GO

CREATE TABLE [TransferTest].[MoveMe] ([Col1] INT);

GO
CREATE PROCEDURE [TransferTest].[SelectFromMoveMe]
AS
SET NOCOUNT ON;
SELECT [Col1] FROM [TransferTest].[MoveMe];
GO

EXEC [TransferTest].[SelectFromMoveMe];

ALTER SCHEMA [dbo] TRANSFER [TransferTest].[MoveMe];

SELECT * FROM [dbo].[MoveMe];

EXEC [TransferTest].[SelectFromMoveMe];
/*
Msg 208, Level 16, State 1, Procedure TransferTest.SelectFromMoveMe, Line X [Batch Start Line Y]
Invalid object name 'TransferTest.MoveMe'.
*/