Summary: Learn the T-SQL syntax for adding metadata descriptions to SQL Server tables and columns using sys.sp_addextendedproperty to improve database documentation.
Documenting Your Database: Adding Comments to SQL Server Objects
Adding descriptive comments to your SQL Server database objects is a best practice for long-term maintenance. These comments (extended properties) provide immediate context to developers and analysts, often appearing directly in tools like SQL Server Management Studio (SSMS).
How to Comment on a Table
Use the script below to define a description for a specific table at the schema level.
-- Add a description to a table
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Contains customer-related data.',
@level0type = N'SCHEMA',
@level0name = 'dbo',
@level1type = N'TABLE',
@level1name = 'tblCustomer';
GO
How to Comment on a Column
To add a comment to a specific column, you must define the hierarchy down to level2.
-- Add a description to a column
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Primary key identifier for the customer.',
@level0type = N'Schema',
@level0name = 'dbo',
@level1type = N'Table',
@level1name = 'tblCustomer',
@level2type = N'Column',
@level2name = 'CustomerID';
GO
Pro Tip: Using the name 'MS_Description' is standard; this allows the text to automatically appear in the "Description" field within the SSMS Table Designer and Object Explorer properties.
For more in-depth guidance on querying all existing comments across SQL Server objects, visit the reference below:
