How Can I Extract SQL Server Table Metadata Including Descriptions Using a Single SQL Query?

Extracting Table Metadata with SQL Descriptions

Introduction

Understanding the structure and content of SQL Server tables is crucial for data management. This article addresses the need to extract comprehensive table metadata, including descriptions, field names, and data types.

SQL Statement

To retrieve the desired metadata, the following SQL statement can be employed:

SELECT
  u.name + '.' + t.name AS [table],
  td.value AS [table_desc],
  c.name AS [column],
  cd.value AS [column_desc]
FROM
  sysobjects t
INNER JOIN
  sysusers u ON u.uid = t.uid
LEFT OUTER JOIN
  sys.extended_properties td ON td.major_id = t.id AND td.minor_id = 0 AND td.name = 'MS_Description'
INNER JOIN
  syscolumns c ON c.id = t.id
LEFT OUTER JOIN
  sys.extended_properties cd ON cd.major_id = c.id AND cd.minor_id = c.colid AND cd.name = 'MS_Description'
WHERE
  t.type = 'u'
ORDER BY
  t.name, c.colorder

Breakdown

• The query begins by selecting the desired columns: table name concatenated with database name, table description, column name, and column description.
• The data is gathered from multiple system tables, including sysobjects (tables), sysusers (database users), and syscolumns (table columns).
• The left outer joins to sys.extended_properties retrieve the extended properties that contain table and column descriptions.
• The WHERE clause filters for user tables (type = 'u').
• The ORDER BY clause sorts the results by table name and column order.

Example Output

The output of the query would produce a table with the following columns:

table	table_desc	column	column_desc
AdventureWorks.Product	Product table	ProductID	Primary key of the product
AdventureWorks.Product	Product table	Name	Product name
AdventureWorks.SalesOrder	Sales order table	OrderID	Unique identifier of the sales order
AdventureWorks.SalesOrder	Sales order table	CustomerID	Customer placing the order

Conclusion

This SQL statement provides a comprehensive approach to extracting table metadata, including descriptions, field names, and data types. By understanding the structure and content of their tables, users can better manage, query, and manipulate their data in SQL Server.

The above is the detailed content of How Can I Extract SQL Server Table Metadata Including Descriptions Using a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!