Return Column Information from a Linked Server in SQL Server (T-SQL Examples)

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