Click here to Skip to main content
13,594,656 members
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 5 May 2012
Licenced CPOL

List SQL Server Table Column Name as Rows

, 6 May 2012
Rate this:
Please Sign up or sign in to vote.
This is an alternative for "List SQL Server table column name as rows".


One other way to fetch information from SQL Server tables is to use the system procedure sp_help. This procedure has been around... as far as I can remember and it's still supported with the newest versions of SQL Server so it can be used basically with all versions (at least starting from 4.2).

Actually sp_help returns several different results about the table. These include basic information about the table, column information, identity information and so on... 

Using sp_help in a SQL Editor like SSMS

Using sp_help in for example SQL Server Management Studio is simple. Just execute the procedure and give the desired table name as a parameter. If needed, also define the schema the table belongs to.

Imagine you have a small table like the following:

   Column1 int,
   Column2 varchar(100)

Now in the same database, execute:

sp_help @objname = 'SomeTable';

This would give you something like the following results:

Name       Owner  Type        Created_datetime
---------  -----  ----------  -----------------------
SomeTable  dbo    user table  2012-05-05 21:43:36.730

Column_name  Type     Computed  Length  Prec  Scale  Nullable  TrimTrailingBlanks  FixedLenNullInSource  Collation
-----------  -------  --------  ------  ----  -----  --------  ------------------  --------------------  ---------
Column1      int      no         4      10    0      yes       (n/a)               (n/a)                 NULL
Column2      varchar  no         100                 yes       no                  yes                   Finnish_Swedish_CI_AS

Identity                     Seed  Increment  Not For Replication
---------------------------  ----  ---------  -------------------
No identity column defined.  NULL  NULL       NULL

No rowguidcol column defined.


So you actually get quite a lot of information about the table. 

Getting the Information from the Program 

If you want to get the column information from a program, you can use the same procedure. The catch here is that since the procedure returns several result sets, you have to move to the proper result set. When getting column information, this would be the second result set.

Also note that MARS must be enabled in order to properly get all of the result sets.

Using C#, the snippet could look like (note the reader.NextResult()): 

System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlDataReader reader;

connection.ConnectionString = "Persist Security Info=False;
Integrated Security=true;Initial Catalog=<YourDatabase>;Server=<YourServer>;
using (command = new System.Data.SqlClient.SqlCommand()) {
   command.CommandText = "sp_help ";
   command.CommandType = System.Data.CommandType.StoredProcedure;
   command.Connection = connection;
   command.Parameters.AddWithValue("objname", "SomeTable");
   using (reader = command.ExecuteReader()) {
      reader.NextResult(); // Move to the next result set
      while (reader.Read()) {
         Console.WriteLine(string.Format("Column name:       
         {0}", reader["Column_name"].ToString()));
         Console.WriteLine(string.Format("Column type:       
         {0}", reader["Type"].ToString()));
         {0}", reader["Computed"].ToString()));
         {0}", reader["Length"].ToString()));
         {0}", reader["Prec"].ToString()));
         {0}", reader["Scale"].ToString()));
         {0}", reader["Nullable"].ToString()));
         Console.WriteLine(string.Format("Trim trail blanks: 
         {0}", reader["TrimTrailingBlanks"].ToString()));
         Console.WriteLine(string.Format("Fixed length null: 
         {0}", reader["FixedLenNullInSource"].ToString()));
         {0}", reader["Collation"].ToString()));

This snippet would give you results like: 

Column name:                 Column1
Column type:                 int
Computed:                    no
Length:                      4
Precision:                   10   
Scale:                       0    
Nullable:                    yes
Trim trailing blanks:        (n/a)
Fixed length null in source: (n/a)

Column name:                 Column2
Column type:                 varchar
Computed:                    no
Length:                      100
Nullable:                    yes
Trim trailing blanks:        no
Fixed length null in source: yes
Collation:                   Finnish_Swedish_CI_AS 

For more information about sp_help, go to sp_help (Transact-SQL).


  • May 5th, 2012: Tip created


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Europe Europe
This member does have enough reputation to be able to display their biography and homepage.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
Member 1026870611-Sep-13 21:01
memberMember 1026870611-Sep-13 21:01 
GeneralRe: My vote of 5 Pin
Mika Wendelius11-Sep-13 21:49
mvpMika Wendelius11-Sep-13 21:49 
QuestionCan't we use Information_Schema for this? Pin
Dinesh Loganathan7-May-12 7:23
memberDinesh Loganathan7-May-12 7:23 
AnswerRe: Can't we use Information_Schema for this? Pin
Mika Wendelius7-May-12 7:51
mvpMika Wendelius7-May-12 7:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.180621.3 | Last Updated 6 May 2012
Article Copyright 2012 by Wendelius
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid