In SQL Server you can use the sp_tables_ex system stored procedure to return table information about the tables from a specified linked server.
The simplest way to execute this stored procedure is to pass the name of the linked server. Doing that will return all tables from the default database on the specified linked server, including system tables and views. This could be a big list.
You also have the option of specifying a different database and/or a specific table schema. You can also filter the results based on the table type (e.g. table, view, system table, etc).
Syntax
The syntax goes like this:
sp_tables_ex [ @table_server = ] 'table_server'
[ , [ @table_name = ] 'table_name' ]
[ , [ @table_schema = ] 'table_schema' ]
[ , [ @table_catalog = ] 'table_catalog' ]
[ , [ @table_type = ] 'table_type' ]
[ , [@fUsePattern = ] 'fUsePattern' ]
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 them in the following examples. For more information on these arguments, see the Microsoft documentation.
Example 1 – Return all Tables
The following example returns all tables, views, system tables, aliases, etc from the default database on the linked server called Homer.
EXEC sp_tables_ex 'Homer';
This returns hundreds of rows on my system, so I won’t list the results in this example. Bear in mind, most of those rows are system tables and system views.
This could also be done like this:
EXEC sp_tables_ex @table_server = 'Homer';
Example 2 – Specify a Different Database
The following example specifies that the WideWorldImportersDW database should be used.
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'WideWorldImportersDW';
Again, this returns hundreds of rows, so I won’t list the results.
Example 3 – Return a Specific Table
In this example, I return information about a specific table.
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'Music', @table_name = 'Artists';
Results:
+-------------+---------------+--------------+--------------+-----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | |-------------+---------------+--------------+--------------+-----------| | Music | dbo | Artists | TABLE | NULL | +-------------+---------------+--------------+--------------+-----------+
Example 4 – Only Return Views
In this example, I specify that only views should be returned.
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'Music', @table_type = 'VIEW';
Results:
+-------------+---------------+--------------+--------------+-----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | |-------------+---------------+--------------+--------------+-----------| | Music | dbo | BluesAlbums | VIEW | NULL | | Music | dbo | JazzAlbums | VIEW | NULL | | Music | dbo | RockAlbums | VIEW | NULL | +-------------+---------------+--------------+--------------+-----------+
Note that these don’t include system views. If I wanted to return system views, I would’ve used @table_type = 'SYSTEM VIEW' (and the result set would be much larger).
The @table_type argument accepts the following types: ALIAS, GLOBAL TEMPORARY, LOCAL TEMPORARY, SYNONYM, SYSTEM TABLE, SYSTEM VIEW, TABLE, and VIEW.
Example 5 – Specify a Table Schema
The following example narrows the results to a specific table schema (Dimension) within the WideWorldImportersDW database.
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'WideWorldImportersDW', @table_schema = 'Dimension';
Results:
+----------------------+---------------+------------------+--------------+-----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | |----------------------+---------------+------------------+--------------+-----------| | WideWorldImportersDW | Dimension | City | TABLE | NULL | | WideWorldImportersDW | Dimension | Customer | TABLE | NULL | | WideWorldImportersDW | Dimension | Date | TABLE | NULL | | WideWorldImportersDW | Dimension | Employee | TABLE | NULL | | WideWorldImportersDW | Dimension | Payment Method | TABLE | NULL | | WideWorldImportersDW | Dimension | Stock Item | TABLE | NULL | | WideWorldImportersDW | Dimension | Supplier | TABLE | NULL | | WideWorldImportersDW | Dimension | Transaction Type | TABLE | NULL | +----------------------+---------------+------------------+--------------+-----------+
Example 6 – Wildcard Characters
The @fUsePattern argument allows you to specify whether or not the %, _, [, and ] characters are interpreted as wildcard characters.
The default value is 1, which means that they are interpreted as wildcard characters. You can specify 0 to specify that they should not be interpreted as wildcard characters.
Here’s an example of using a wildcard character:
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'WideWorldImportersDW', @table_name = 'C%', @table_type = 'TABLE', @fUsePattern = 1;
Results:
+----------------------+---------------+------------------+--------------+-----------+ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | |----------------------+---------------+------------------+--------------+-----------| | WideWorldImportersDW | Dimension | City | TABLE | NULL | | WideWorldImportersDW | Dimension | Customer | TABLE | NULL | | WideWorldImportersDW | Integration | City_Staging | TABLE | NULL | | WideWorldImportersDW | Integration | Customer_Staging | TABLE | NULL | +----------------------+---------------+------------------+--------------+-----------+
And here’s what happens if I specify no wildcard characters:
EXEC sp_tables_ex @table_server = 'Homer', @table_catalog = 'WideWorldImportersDW', @table_name = 'C%', @table_type = 'TABLE', @fUsePattern = 0;
Results:
(0 rows affected) Time: 0.324s