Click here to Skip to main content
Click here to Skip to main content
Alternative Tip

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".

Introduction

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:

   CREATE TABLE SomeTable (
      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

RowGuidCol
-----------------------------
No rowguidcol column defined.

Data_located_on_filegroup
-------------------------
PRIMARY 

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>;
MultipleActiveResultSets=true";
connection.Open();
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()));
         Console.WriteLine(string.Format("Computed:          
         {0}", reader["Computed"].ToString()));
         Console.WriteLine(string.Format("Length:            
         {0}", reader["Length"].ToString()));
         Console.WriteLine(string.Format("Precision:         
         {0}", reader["Prec"].ToString()));
         Console.WriteLine(string.Format("Scale:             
         {0}", reader["Scale"].ToString()));
         Console.WriteLine(string.Format("Nullable:          
         {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()));
         Console.WriteLine(string.Format("Collation:         
         {0}", reader["Collation"].ToString()));
         Console.WriteLine();
     }
   }
}
connection.Close(); 

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)
Collation:                   

Column name:                 Column2
Column type:                 varchar
Computed:                    no
Length:                      100
Precision:                        
Scale:                            
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).

History

  • May 5th, 2012: Tip created

License

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

Share

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.
 
However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).
 
For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 1026870611-Sep-13 21:01 
GeneralRe: My vote of 5 PinmvpMika Wendelius11-Sep-13 21:49 
QuestionCan't we use Information_Schema for this? PinmemberDinesh Loganathan7-May-12 7:23 
AnswerRe: Can't we use Information_Schema for this? PinmvpMika Wendelius7-May-12 7:51 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 6 May 2012
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid