How to add comments or remarks to tables in Microsoft SQL Server?

Data and Databases
1

I’m trying to figure out how to add comments or remarks to tables in Microsoft SQL Server. When I use sp_tables, I see a remark field. But I can’t seem to find a way to populate it. I’ve tried the COMMENT ON TABLE syntax that works in other SQL databases, but it’s not valid in SQL Server. Here’s what I’ve attempted:

-- Check table info
EXEC sp_tables my_example_table;

-- Attempt to add a remark (doesn't work)
COMMENT ON TABLE my_example_table IS 'Sample description';

Is there a built-in way to add table remarks in SQL Server? I’d prefer not to create a separate data dictionary table if possible. Any suggestions would be appreciated!

2

hm, interesting question! have u considered using database diagrams? they let u add descriptions to tables visually. might be easier than messing with extended properties. plus, it’s great for documentation. what kinda info are u trying to add to ur tables? maybe there’s a creative solution we haven’t thought of yet?

3

hey, u can use extended properties. it’s not as easy as COMMENT ON TABLE but it works. try:

EXEC sp_addextendedproperty @name=N’MS_Description’, @value=N’ur table description’, @level0type=N’SCHEMA’, @level0name=‘dbo’, @level1type=N’TABLE’, @level1name=‘my_example_table’

hope that helps!

4

Unfortunately, SQL Server doesn’t have a built-in mechanism for adding comments directly to tables like some other database systems do. However, there’s a workaround you can use: extended properties. These allow you to add custom metadata to database objects, including tables. Here’s how you can do it:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Your table description here',
    @level0type = N'SCHEMA', @level0name = 'dbo',
    @level1type = N'TABLE',  @level1name = 'my_example_table'

To retrieve the comment later, you can use sp_helptext or query the sys.extended_properties view. While not as straightforward as COMMENT ON TABLE, this method achieves the same goal of documenting your tables within the database itself.