Sql-server – Is it possible to attach a description field to the records in INFORMATION_SCHEMA.TABLES

sql serversql server 2014ssms

In SQL Server, [database name].INFORMATION_SCHEMA.TABLES has the following columns:

  • TABLE_CATALOG
  • TABLE_SCHEMA
  • TABLE_NAME
  • TABLE_TYPE

This table has no additional fields, such as "DESCRIPTION"

This is confirmed by MSDN.

My question is this: other than making a manual database or a Word document of my own, is there a system or other column or table that I can use to store a "description" or "memo" value per record in the INFORMATION_SCHEMA.TABLES table?

To deal with this as much as possible I use database, table, and field naming conventions, but that can go only so far. Some of the names have a "back story" that can be explained to future team members in a "description" field.

I have tried so far:

  • Google searches for related questions.
  • Looking for a "description" field of the table properties in SSMS (see screenshot below), but it wasn't what I had in mind.

Is there any preexisting field in a system or other table that I am missing and matches my need?

SSMS Table Properties

Best Answer

Use extended properties. It's not in the INFORMATION_SCHEMA views but it will stay attached to that object.

I wrote about it in detail here: https://sqlstudies.com/2015/05/18/extending-your-metadata-with-extended-properties/

But here is what it would look like attached to a User:

enter image description here

Here is how you would query all of the extended properties for users in a given database:

-- View the extended properties
SELECT ep.*, dp.name FROM sys.extended_properties ep
JOIN sys.database_principals dp
    ON ep.major_id = dp.principal_id
WHERE class_desc = 'DATABASE_PRINCIPAL'
GO

The same thing will work for pretty much any object in SQL Server. Obviously you'd want to modify the query somewhat but that should be pretty easy.

Related Question