How do I get a list of columns in a table or view?

Question

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?

Answer 1

Another way is querying the INFORMATION_SCHEMA.columns view as detailed here:

Information_Schema - COLUMNS

This will give you information for all the columns in the current database (and what table/view they belong to) including their datatypes, precision, collation and whether they allow nulls etc

Usefully as well, these views are maintained in multiple DBMS programs too, so you could potentially use the same or similar query to get the same information regarding a MySQL database as you can a SQL Server DB, which could be useful if you are developing on multiple platorms.

Answer 2

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')

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