In SQL Server you can use the sp_columns system stored procedure to return column information for the specified objects that can be queried in the current environment. Such a objects include tables, views, or other objects that have columns such as table-valued functions.
You can get information for a specific column, or you can specify all columns from a given table, view, etc.
Syntax
The syntax goes like this:
sp_columns [ @table_name = ] object
[ , [ @table_owner = ] owner ]
[ , [ @table_qualifier = ] qualifier ]
[ , [ @column_name = ] column ]
[ , [ @ODBCVer = ] ODBCVer ]
The @table_name argument is the only required argument. This is the name of the table/object that you want the column information from.
The other arguments are optional. For more information on these arguments, see the Microsoft documentation.
This stored procedure requires SELECT and VIEW DEFINITION permissions on the schema.
Example 1 – Return Information for a Specific Column
This example uses all possible arguments. It returns information for a specific column, in a specific table, from a specific table owner, in a specific database.
EXEC sp_columns @table_name = 'Cities', @table_owner = 'Application', @table_qualifier = 'WideWorldImporters', @column_name = 'Location', @ODBCVer = 2;
Result (using vertical output):
TABLE_QUALIFIER | WideWorldImporters TABLE_OWNER | Application TABLE_NAME | Cities COLUMN_NAME | Location DATA_TYPE | -4 TYPE_NAME | geography PRECISION | 2147483647 LENGTH | 2147483647 SCALE | NULL RADIX | NULL NULLABLE | 1 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | -4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | 2147483647 ORDINAL_POSITION | 4 IS_NULLABLE | YES SS_DATA_TYPE | 23 (1 row affected)
Here’s a more concise way of doing it:
EXEC sp_columns 'Cities', 'Application', 'WideWorldImporters', 'Location', 2;
That returns the same results.
Example 2 – Specify a Table Only
In this example I switch to another database and only specify the table name.
USE Music; EXEC sp_columns @table_name = 'Artists';
Result (using vertical output):
Changed database context to 'Music'. -[ RECORD 1 ]------------------------- TABLE_QUALIFIER | Music TABLE_OWNER | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistId DATA_TYPE | 4 TYPE_NAME | int identity PRECISION | 10 LENGTH | 4 SCALE | 0 RADIX | 10 NULLABLE | 0 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | 4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 1 IS_NULLABLE | NO SS_DATA_TYPE | 56 -[ RECORD 2 ]------------------------- TABLE_QUALIFIER | Music TABLE_OWNER | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar PRECISION | 255 LENGTH | 510 SCALE | NULL RADIX | NULL NULLABLE | 0 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | -9 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | 510 ORDINAL_POSITION | 2 IS_NULLABLE | NO SS_DATA_TYPE | 39 -[ RECORD 3 ]------------------------- TABLE_QUALIFIER | Music TABLE_OWNER | dbo TABLE_NAME | Artists COLUMN_NAME | ActiveFrom DATA_TYPE | -9 TYPE_NAME | date PRECISION | 10 LENGTH | 20 SCALE | NULL RADIX | NULL NULLABLE | 1 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | -9 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 3 IS_NULLABLE | YES SS_DATA_TYPE | 0 (3 rows affected)
This returns information for all columns in the specified table.
You do need to be in the correct database though. If I re-run the previous example on a different database, I get no results.
USE WideWorldImporters; EXEC sp_columns @table_name = 'Artists';
Result:
Changed database context to 'WideWorldImporters'. (0 rows affected)
Example 3 – About the Table Qualifier
If you provide the @table_qualifier argument, it must be the same as the current database. If it’s not, an error is returned.
USE Music; EXEC sp_columns @table_name = 'Artists', @table_qualifier = 'WideWorldImporters';
Results:
Msg 15250, Level 16, State 1, Line 24 The database name component of the object qualifier must be the name of the current database.
In this example I switched to the “Music” database, but then used a table qualifier of “WideWorldImporters”, which resulted in error Msg 15250 being returned.
In this case, I should have used a table qualifier of “Music”. Alternatively, I could have omitted the argument altogether.
Example 4 – Views
The syntax is the same, regardless of the object type. Here’s an example of getting column information for a view:
EXEC sp_columns @table_name = 'Customers', @table_owner = 'Website', @column_name = 'CustomerID';
Results:
TABLE_QUALIFIER | WideWorldImporters TABLE_OWNER | Website TABLE_NAME | Customers COLUMN_NAME | CustomerID DATA_TYPE | 4 TYPE_NAME | int PRECISION | 10 LENGTH | 4 SCALE | 0 RADIX | 10 NULLABLE | 0 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | 4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 1 IS_NULLABLE | NO SS_DATA_TYPE | 56
That happens to be column information for a view.
In this case, if I omit the table owner from the query, two rows are returned:
EXEC sp_columns @table_name = 'Customers', @column_name = 'CustomerID';
Results:
-[ RECORD 1 ]------------------------- TABLE_QUALIFIER | WideWorldImporters TABLE_OWNER | Sales TABLE_NAME | Customers COLUMN_NAME | CustomerID DATA_TYPE | 4 TYPE_NAME | int PRECISION | 10 LENGTH | 4 SCALE | 0 RADIX | 10 NULLABLE | 0 REMARKS | NULL COLUMN_DEF | (NEXT VALUE FOR [Sequences].[CustomerID]) SQL_DATA_TYPE | 4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 1 IS_NULLABLE | NO SS_DATA_TYPE | 56 -[ RECORD 2 ]------------------------- TABLE_QUALIFIER | WideWorldImporters TABLE_OWNER | Website TABLE_NAME | Customers COLUMN_NAME | CustomerID DATA_TYPE | 4 TYPE_NAME | int PRECISION | 10 LENGTH | 4 SCALE | 0 RADIX | 10 NULLABLE | 0 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | 4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 1 IS_NULLABLE | NO SS_DATA_TYPE | 56 (2 rows affected)
Two rows are returned because there are two objects called “Customers”. One is a view and the other is a table. The table owner for the view is called “Website”, and table owner for the table is called “Sales”.
Example 5 – Table-Valued Functions
As mentioned, you can also get column information for a table-valued function.
Here’s an example of getting column information for a table-valued function. Once again, the syntax is the same.
EXEC sp_columns @table_name = 'DetermineCustomerAccess';
Results:
TABLE_QUALIFIER | WideWorldImporters TABLE_OWNER | Application TABLE_NAME | DetermineCustomerAccess COLUMN_NAME | AccessResult DATA_TYPE | 4 TYPE_NAME | int PRECISION | 10 LENGTH | 4 SCALE | 0 RADIX | 10 NULLABLE | 0 REMARKS | NULL COLUMN_DEF | NULL SQL_DATA_TYPE | 4 SQL_DATETIME_SUB | NULL CHAR_OCTET_LENGTH | NULL ORDINAL_POSITION | 1 IS_NULLABLE | NO SS_DATA_TYPE | 56