There are numerous situations in the DBMSs  world where you may need to utilize a connected server between two diverse SQL Server 2014 cases with a specific end goal to execute immediate queries between them. In any case, the majority of T-SQL auto-complete tools experience issues to rundown the items (i.e. tables) of the connected server's database.

An approach to see these items, is to browse via SSMS's Object Explorer. Shouldn't we think about however in the event that you need to have a quick view of all the accessible tables in the connected server's database while composing your T-SQL script?

The answer is simple! You can do this by utilizing the Information Schema Views. Underneath you will discover a pstored procedure that I made which can help you to list  all the tables of linked server's database. And here is the code:
------------------------------------------------------------------------------------------
-- Sample Code for Blog Post: Listing all Tables of a Linked Server's Database
-- Disclaimer: This is a sample code. Do not use it in Production Systems before properly
-- Testing it. You bear sole responsibility for usage of any content of this T-SQL code.
-- You are responsible for any loss of data, loss of peace or any damages due to usage of this code. Always take backup of your data.
------------------------------------------------------------------------------------------
USE [ENTER_DATABASE_NAME];
GO
CREATE PROCEDURE [dbo].[DBTableInfo] (@LinkedServerName varchar(50), @DBName varchar(50))
AS
--variables declaration
DECLARE @LinkedServerNameFiltered VARCHAR(50)
DECLARE @LinkedServerNameFound int
DECLARE @DBsFound int
DECLARE @DBNameFiltered varchar(50)
DECLARE @Command nvarchar(500)
--check if specified linked server exists
SET @LinkedServerNameFiltered=QUOTENAME(@LinkedServerName);
SET @LinkedServerNameFound=0;
SET @LinkedServerNameFound=(SELECT COUNT(*) FROM sys.servers s WHERE s.name=@LinkedServerName);
--report findings
IF @LinkedServerNameFound=0
BEGIN
PRINT 'Error: Linked server ' + @LinkedServerNameFiltered+ ' not found.';
RETURN;
END
--check if specified database exists
DECLARE @QUERY nvarchar(250);
SET @DBNameFiltered = QUOTENAME(@DBName,'''');
SET @DBsFound=0;
SET @QUERY='(SELECT @DBs=COUNT(*) FROM ' + @LinkedServerNameFiltered +'.[master].sys.sysdatabases s WHERE s.name='+@DBNameFiltered+')';
EXEC sp_executesql @QUERY, N'@DBs int OUTPUT', @DBs=@DBsFound OUTPUT;
--report findings
IF @DBsFound=0
BEGIN
PRINT 'Error: Database ' + @DBNameFiltered + ' not found.';
RETURN;
END
--construct dynamic T-SQL statement
SET @DBNameFiltered = QUOTENAME(@DBName);
SET @LinkedServerNameFiltered=QUOTENAME(@LinkedServerName);
SET @Command= 'SELECT TABLE_SCHEMA as TableSchema, TABLE_NAME as TableName,(''SELECT TOP 10 * FROM '+ @LinkedServerNameFiltered +'.'+ @DBNameFiltered +'.'' + QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)) as SampleQuery
FROM ' + @LinkedServerNameFiltered+'.' + @DBNameFiltered+'.INFORMATION_SCHEMA.TABLES i WHERE i.TABLE_TYPE LIKE ''%TABLE%'' ORDER BY 1';
--execute the command
EXEC sp_executesql @command;
------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

You can call the stored procedure as follows:
USE [ENTER_DATABASE_NAME];
GO
EXEC [dbo].[DBTableInfo] 'LINKED_SERVER_NAME', 'LINKED_SERVER_DB'
GO


The stored procedure shows three columns for each each record:(i) Table Schema, (ii) Table Name, (iii) A sample question that can be executed and gives back where the top 10 rows for the specific table.. And here is the output:

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.