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?
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)
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’
CREATE TABLE #Worksheets (TABLE_CAT varchar(30), TABLE_SCHEM VARCHAR(30), TABLE_NAME VARCHAR(30), TABLE_TYPE VARCHAR(30), REMARKS VARCHAR(80))
INSERT INTO #Worksheets
@table_server = ‘MY_AS400_LINK’
, @table_catalog = ‘MYCATNAME’
, @table_type = ‘TABLE’
select * from #Worksheets