CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
rory.ap
rory.ap

Reputation: 35260

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

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?

Upvotes: 4

Views: 22926

Answers (7)

Markus Dutschke
Markus Dutschke

Reputation: 10596

simple list of column names without any further information.

SELECT COLUMN_NAME FROM TABLENAME.INFORMATION_SCHEMA.COLUMNS;

Replace TABLENAME with your tables name.

Upvotes: 1

rory.ap
rory.ap

Reputation: 35260

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

Upvotes: 3

Andrew
Andrew

Reputation: 8693

exec sp_helptext <your view name>

Also works for the view only, blachniet's answer is best if you need details on the columns in the table.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

To get a list of Columns of a view with some other information about the column you can use the following:

SELECT * FROM sys.columns c, sys.views v
   WHERE c.object_id = v.object_id
   AND v.name = 'view_Name'
GO

And if you only want the list of Column Name use this.

SELECT c.name 
FROM sys.columns c, sys.views v
WHERE c.object_id = v.object_id
AND v.name = 'view_UserAssessphers'
GO

Upvotes: 2

Ryan Anderson
Ryan Anderson

Reputation: 154

In a new query window, type the name of the view/table, highlight it, and press Alt-F1. This will run sp_help, like blachniet suggested.

Upvotes: 0

steoleary
steoleary

Reputation: 9278

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.

Upvotes: 4

blachniet
blachniet

Reputation: 4543

sp_columns returns detailed information about each of the columns in the table. SO Answer

sp_columns @tablename

sp_help returns detailed information about the entire table including the columns and constraints. SO Answer

sp_help @tablename

Upvotes: 1

Related Questions