如何获取表或视图中的列列表? [英] How do I get a list of columns in a table or view?

问题描述

有时，我有兴趣获取 SQL Server 2008 R2 数据库中某个表或视图中的列列表.例如，如果您在不使用昂贵的现成产品的情况下构建数据库文档，这将非常有用.

On occasion, I'm interested in getting a list of columns in one of the tables or views in my SQL Server 2008 R2 database. It's useful, for example, if you're building database documentation without using an expensive off-the-shelf product.

获取此信息的简单方法是什么?

What's an easy way to get this information?

推荐答案

在 SQL Server 2008 R2(以及其他版本)中，每个数据库都会自动提供系统视图.只要你连接到你的表所在的数据库，你就可以运行这样的查询:

In SQL Server 2008 R2 (among other versions), there are system views provided automatically with every database. As long as you are connected to the database where your table resides, you can run a query like this:

DECLARE @TableViewName NVARCHAR(128)
SET @TableViewName=N'MyTableName'

SELECT b.name AS ColumnName, c.name AS DataType, 
b.max_length AS Length, c.Precision, c.Scale, d.value AS Description
FROM sys.all_objects a
INNER JOIN sys.all_columns b
ON a.object_id=b.object_id
INNER JOIN sys.types c
ON b.user_type_id=c.user_type_id
LEFT JOIN sys.extended_properties d
ON a.object_id=d.major_id AND b.column_id=d.minor_id AND d.name='MS_Description'
WHERE a.Name=@TableViewName
AND a.type IN ('U','V')

当然，这只是一个起点.每个数据库中还有许多其他系统视图和列可用.您可以通过 SQL Server Management Studio 在 Views > 下找到它们."系统视图

Of course, this is just a starting point. There are many other system views and columns available in every database. You can find them through SQL Server Management Studio under Views > "System Views

这篇关于如何获取表或视图中的列列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！