How To describe table structure in SQL Server

September 09, 2012 0 Comments

This post is about sp_columns which is used to describe the table structure of a SQL Server table.
The simplest way to use sp_columns to show the columns and related information about a SQL Server table is to execute the stored proecedure passing it the table name like so:

exec sp_columns MyTable

This returns a row for each column in the table, describing the column in detail.

The sp_columns stored procedure can take additional arguments to the table name. You can also pass the table owner, table qualifier (i.e. the database name), column name and the ODBC version used. The table owner and column name parameters support wildcard pattern matching, so you can use % and _

For example, if you only wanted to query the "foo" column from the above example, you would do this:

exec sp_columns MyTable, @column_name = 'foo'

If you wanted to query all columns which started with the letter "a" you could do the following:

exec sp_columns MyTable, @column_name = 'a%'
How To describe table structure in SQL Server

Prakash Hari Sharma
I'm a software developer and Java enthusiast. I like clean and modular code, enjoy Agile projects and have a passion for trying out new things. I try to learn and teach every day, and occasionally I'm even able to blog about it. :)