Describe table structure with MS SQL ServerDescribe table structure with MS SQL Server

Posted July 23rd, 2008 in Microsoft SQL Server

This is the second in a series of three posts about using the sp_tables, sp_columns and sp_stored_procedures stored procedures with Microsoft SQL Server databases. 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. Some example output can be seen below:

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
MyDB dbo MyTable foo 12 varchar 10 10           12   10 1 NO  39
MyDB dbo MyTable bar 12 varchar 16 16           12   16 2 NO  39

You can read more information about what each column returned means in the MSDN documentation about this stored procedure.

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%'

That's a basic overview of the sp_columns stored procedure for describing a table structure in Microsoft SQL Server. The final post in this series (in a week's time) will look at sp_stored_procedures to get a list of stored procedures available.

Recent posts: