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).