How to Retrieve Table Descriptions in SQL Server 2016

Описание: Discover how to efficiently query table descriptions in SQL Server 2016 with our simple, step-by-step guide.
---
This video is based on the question https://stackoverflow.com/q/68025026/ asked by the user 'Gargoyle' ( https://stackoverflow.com/u/1455351/ ) and on the answer https://stackoverflow.com/a/68025300/ provided by the user 'Gargoyle' ( https://stackoverflow.com/u/1455351/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Get table description via SQL query

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Retrieve Table Descriptions in SQL Server 2016

If you're working on generating classes automatically with Microsoft SQL Server 2016, you might find yourself needing to extract more than just the basic structure of your database tables. One crucial piece of information that’s often overlooked is the table descriptions. These descriptions help you understand the context of the data stored within each table. Unfortunately, retrieving this information isn't as straightforward as querying the sys.tables table. In this guide, we’ll guide you step-by-step on how to extract table descriptions using SQL.

Understanding Table Descriptions in SQL Server

In SQL Server, metadata about tables can be found in catalog views and system tables. However, the description property of a table is stored in extended properties, which can make it a bit tricky to access. The sys.tables view contains basic details about the tables, but it doesn’t hold the descriptions directly, which is where the sys.extended_properties come into play.

Why Use Table Descriptions?

Table descriptions are particularly helpful for several reasons:

Documentation: Clearly define the purpose of each table.

Collaboration: Help your team understand the database structure better.

Automation: Facilitate automated processes such as class generation.

How to Query Table Descriptions

Now that we understand what table descriptions are and why they are important, let’s go through the SQL query needed to extract this information. The SQL code below retrieves the names of the tables and their descriptions.

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the SQL Query

Let’s dissect the SQL query to understand how it works:

Selecting Table Information:

SELECT t.name, CONVERT(VARCHAR(MAX), p.value): This part selects the table name and the description (converted to a maximum-length string) from the results.

From the Right Tables:

FROM sys.tables t: We're querying from the sys.tables view to get basic information about the tables.

Join to Get Extended Properties:

JOIN sys.extended_properties p ON t.object_id = p.major_id: This join connects the table information to its extended properties using the object_id.

Filter User Tables:

WHERE t.type_desc = 'USER_TABLE': We specify that we're only interested in user-defined tables.

Filter for Descriptions:

We check that the property name is MS_Description and ensure it has a valid length (LEN(CONVERT(VARCHAR(MAX), p.value)) > 0), filtering out any empty descriptions.

Running the Query

To run this query, follow these steps:

Open SQL Server Management Studio (SSMS).

Connect to your SQL Server instance.

Select the database where your target tables are stored.

Paste the query into the query window and execute it.

The results will display a list of all user-defined tables along with their corresponding descriptions, allowing you to use this information as needed for your class generation automation.

Conclusion

Finding the table description in SQL Server 2016 requires a specialized query that combs through extended properties. With the information provided in this article, you can easily integrate table descriptions into your database automation processes. Having clarity on what each table represents will undoubtedly enhance your overall database management strategy, providing a solid foundation for further development and collaboration.

Now you can quickly and efficiently retrieve table descriptions from your SQL Server database! Happy querying!