The System Procedure sp_tables_ex lists table metadata from an existing linked server. I check its provider and login mapping separately.
-- Requires an existing authorized linked server and provider metadata support.
EXEC sys.sp_tables_ex @table_server = N'YourLinkedServer',
@table_catalog = N'YourDatabase', @table_type = N'TABLE';
EXEC sys.sp_tables_ex @table_server = N'YourLinkedServer',
@table_catalog = N'YourDatabase', @table_type = N'VIEW';Replace the linked-server and catalog names. TABLE asks for tables; VIEW asks for views. Provider support and the mapped remote login determine what is returned. An empty result does not prove that no tables exist on the remote server.
This procedure reads provider metadata and does not create a linked server. Configure and review the connection and login mapping separately. For local objects, sp_tables uses the current server and database.
My system-procedure toolbox offers additional starting points. Procedures, views and triggers still serve different purposes. I choose them from the required behavior, rather than familiarity with one. Any claimed bandwidth benefit needs the actual design behind it.
Related reading
- 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
Remote metadata is not remote access permission, it is information returned under an existing connection identity.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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’
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
EXEC sp_tables_ex
@table_server = ‘MY_AS400_LINK’
, @table_catalog = ‘MYCATNAME’
, @table_type = ‘TABLE’
select * from #Worksheets