SQL SERVER – System Procedure to List Out Table From Linked Server

There is a system procedure named sp_tables which is used to list out the tables available in the CURRENT database of the CURRENT server. But did you know that there exists another system stored procedure that can be used to list out the tables of database available in the linked server?

SQL SERVER - System Procedure to List Out Table From Linked Server systemspforlinkedserver

You can use the system stored procedure named sp_tables_ex.

The following returns list of tables available in the specified Server and database.

EXEC sp_tables_ex
@table_server                     = 'your_linked_server_name'
, @table_catalog                  = 'your_database'
, @table_type                     = 'TABLE'

You can also use the same stored procedure to know the list of Views.

EXEC sp_tables_ex
@table_server    = 'your_linked_server_name'
, @table_catalog = 'your_database'
, @table_type    = 'VIEW'

I would love to know if you have used any of the SP for your remote linked server. Please leave a comment and let me know.

Here are a few additional resources:

SQL SERVER – System Stored Procedures I Use to Get Started
SQL SERVER – System procedures to know SQL Server Version
SQL SERVER – How to use Procedure sp_user_counter1 to sp_user_counter10
SQL SERVER – Stored Procedures Advantages and Best Advantage

I believe the biggest advantage of the stored procedure is that it saves lots of network bandwidth conservation. It has been a long time since I have used views or triggers since I am very comfortable with Stored Procedures.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Linked Server, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – How to Get Updated Link for Code and Database Samples?
Next Post
SQL SERVER – How to Find UNIQUE Key Columns? – sp_special_columns

Related Posts

2 Comments. Leave new

  • Best example I found thus far. Worked perfectly, but is there a way to direct the output to a table rather than the message window?

  • — I found the answer to my question:

    DECLARE @table_server as VARCHAR(30)
    DECLARE @table_catalog as VARCHAR(30)
    DECLARE @table_type as VARCHAR(30)
    SET @table_server = ‘MY_AS400_LINK’
    SET @table_catalog = ‘MYCATNAME’
    SET @table_type = ‘TABLE’


    INSERT INTO #Worksheets
    EXEC sp_tables_ex
    @table_server = ‘MY_AS400_LINK’
    , @table_catalog = ‘MYCATNAME’
    , @table_type = ‘TABLE’

    select * from #Worksheets


Leave a Reply