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.