如何获取表或视图中的列列表?

本文介绍了如何获取表或视图中的列列表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有时，我有兴趣获取 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