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();
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