2

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?

rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • A lot of the answers provided here are great, but I would argue that one of the most important pieces of information for, say, db documentation is the column description which none of the other answers provide. – rory.ap Oct 30 '13 at 16:30
  • Have you actually tried any of the suggested methods? The way I have suggested will give you Column Description as follows "object_id,name,column_id,system_type_id,user_type_id,max_length,precision,scale,collation_name,is_nullable,is_ansi_padded,is_rowguidcol,is_identity,is_computed,is_filestream,is_replicated,is_non_sql_subscribed,is_merge_published,is_dts_replicated and so onnnnnnnnnn is it not enough column description ???? – M.Ali Oct 30 '13 at 17:21
  • In total it is returning almost 50 column all containing detailed inforamtion about every column in the view. seems enough "Column Description" to me pal. – M.Ali Oct 30 '13 at 17:23
  • @MuhammedAli -- No need to get offensive. Of course I tried all the suggested methods. What I meant by column description is shown by the "Description" column in the answer I posted. It comes from the sys.extended_properties view. You can configure a description on your columns within SQL Server Management Studio, for example, when your designing the table. It's one of the fields in the "Properties" list below the table definition. – rory.ap Oct 30 '13 at 17:38
  • In fact, you might be interested to know you can also define a description on every table, again using SSMS as an example, by hitting F4 while designing the table to show the table properties. It's again one of the fields in the properties list. – rory.ap Oct 30 '13 at 17:40
  • AND, you can even define a description on an entire database by right-clicking on the database in the object explorer, selecting "Properties", going to the "Extended Properties" page, and manually entering "MS_Description" into the list of properties. – rory.ap Oct 30 '13 at 17:43
  • Descriptions on database objects are like comments in code. It's just good form and should be used as much as possible. That's what I meant above when I referred to building database documentation. – rory.ap Oct 30 '13 at 17:44

7 Answers7

3

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

rory.ap
  • 34,009
  • 10
  • 83
  • 174
2

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.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • Just like with blachniet's answer, this is a good solution as well, but it still doesn't provide access to the column description as far as I can see. – rory.ap Oct 30 '13 at 16:33
  • Ah, you didn't mention extended properties in your question, in that case joining sys.columns and sys.extended_properties should do the trick. The information_schema views are worth remembering though as they expose a lot of information and like I mentioned, also exist in other DBMSs such as MySQL – steoleary Oct 30 '13 at 19:33
1

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

Community
  • 1
  • 1
blachniet
  • 4,323
  • 5
  • 33
  • 34
1

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
M.Ali
  • 67,945
  • 13
  • 101
  • 127
1
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.

Andrew
  • 8,445
  • 3
  • 28
  • 46
0

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.

Ryan Anderson
  • 154
  • 5
0

simple list of column names without any further information.

SELECT COLUMN_NAME FROM TABLENAME.INFORMATION_SCHEMA.COLUMNS;

Replace TABLENAME with your tables name.

Markus Dutschke
  • 9,341
  • 4
  • 63
  • 58