Describe table structure with MS 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_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEDATA_TYPETYPE_NAMEPRECISIONLENGTHSCALERADIXNULLABLEREMARKSCOLUMN_DEFSQL_DATA_TYPESQL_DATETIME_SUBCHAR_OCTET_LENGTHORDINAL_POSITIONIS_NULLABLESS_DATA_TYPE
MyDBdboMyTablefoo12varchar1010     12 101NO 39
MyDBdboMyTablebar12varchar1616     12 162NO 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.