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 5^th, 2012: Tip created