In SQL Server you can use the sp_columns_ex
system stored procedure to return column information about the columns from a specified linked server.
You can specify an individual column, or you can specify all columns from a given database, table, etc.
Syntax
The syntax goes like this:
sp_columns_ex [ @table_server = ] 'table_server' [ , [ @table_name = ] 'table_name' ] [ , [ @table_schema = ] 'table_schema' ] [ , [ @table_catalog = ] 'table_catalog' ] [ , [ @column_name = ] 'column' ] [ , [ @ODBCVer = ] 'ODBCVer' ]
The @table_server
argument is the only required argument. This is the name of the linked server that you want the table information from.
The other arguments are optional, and I cover most of them in the following examples. For more information on these arguments, see the Microsoft documentation.
Example 1 – Return a Specific Column
The following example returns information about a specific column.
EXEC sp_columns_ex @table_server = 'Homer', @table_name = 'Artists', @table_schema = 'dbo', @table_catalog = 'Music', @column_name = 'ArtistName';
Result (using vertical output):
TABLE_CAT | Music TABLE_SCHEM | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar COLUMN_SIZE | 255 BUFFER_LENGTH | 510 DECIMAL_DIGITS | NULL NUM_PREC_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
In this case, the server name is Homer
, the database name is Music
, the table name is Artists
, the table schema is dbo
and the column name is ArtistName
.
This could also be done like this:
EXEC sp_columns_ex 'Homer', 'Artists', 'dbo', 'Music', 'ArtistName';
Example 2 – Specify a Table Only
In this example I only specify the table name.
EXEC sp_columns_ex @table_server = 'Homer', @table_name = 'Artists';
This returns info about all columns in the Artists table.
Example 3 – Specify the Database & Column Name
In this example I specify the database and column, but not the table.
EXEC sp_columns_ex @table_server = 'Homer', @table_catalog = 'Music', @column_name = 'ArtistName';
Results (using vertical output):
-[ RECORD 1 ]------------------------- TABLE_CAT | Music TABLE_SCHEM | dbo TABLE_NAME | Artists COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar COLUMN_SIZE | 255 BUFFER_LENGTH | 510 DECIMAL_DIGITS | NULL NUM_PREC_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 2 ]------------------------- TABLE_CAT | Music TABLE_SCHEM | dbo TABLE_NAME | BluesAlbums COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar COLUMN_SIZE | 255 BUFFER_LENGTH | 510 DECIMAL_DIGITS | NULL NUM_PREC_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_CAT | Music TABLE_SCHEM | dbo TABLE_NAME | JazzAlbums COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar COLUMN_SIZE | 255 BUFFER_LENGTH | 510 DECIMAL_DIGITS | NULL NUM_PREC_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 4 ]------------------------- TABLE_CAT | Music TABLE_SCHEM | dbo TABLE_NAME | RockAlbums COLUMN_NAME | ArtistName DATA_TYPE | -9 TYPE_NAME | nvarchar COLUMN_SIZE | 255 BUFFER_LENGTH | 510 DECIMAL_DIGITS | NULL NUM_PREC_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
This returned three more columns. These happen to be from three different views (the database has three views with an ArtistName
column: BluesAlbums
, JazzAlbums
, and RockAlbums
).
Example 4 – Specify a Database Only
Here I only specify the database:
EXEC sp_columns_ex @table_server = 'Homer', @table_catalog = 'Music';
I won’t display the results here because it returned almost 6,000 rows. Most of these were from the sys
table schema.
Example 5 – Specify a Table Schema
The following example narrows the results to a specific table schema (dbo
).
EXEC sp_columns_ex @table_server = 'Homer', @table_schema = 'dbo';
This returned a much smaller result set than the previous example. It’s still quite large, so I won’t display it here.
Example 6 – Wildcard Characters
You can also use wildcard characters. Here’s an example of using the %
wildcard character:
EXEC sp_columns_ex @table_server = 'Homer', @table_schema = 'dbo', @table_catalog = 'Music', @column_name = 'Ar%';
This returns all columns that begin with Ar
. In my case, it returned two ArtistId columns and four ArtistName columns.
I could narrow this down:
EXEC sp_columns_ex @table_server = 'Homer', @table_schema = 'dbo', @table_catalog = 'Music', @column_name = 'Ar%Name';
This returned just the ArtistName columns.
However, if I remove the r
:
EXEC sp_columns_ex @table_server = 'Homer', @table_schema = 'dbo', @table_catalog = 'Music', @column_name = 'A%Name';
I now get four extra columns called AlbumName (as well as the ArtistName columns).