Listing all Tables of a Linked Server’s Database

A way to see these objects, is to browse via SSMS’s Object Explorer. What about however if you want to have a quick view of all the available tables in the linked server’s database while writing your T-SQL script?

The answer is easy! You can do this by using the Information Schema Views. Below you will find a stored procedure that I created which can help you to list all the tables of linked server’s database.

Here’s the code:

——————————————————————————————
— Sample Code for Blog Post: Listing all Tables of a Linked Server’s Database
— Blog URL: http://aartemiou.blogspot.com
— Author: Artemakis Artemiou, SQL Server MVP
— Email Address: a_artemiou@hotmail.com
— Official Website: http://www.aartemiou.com
—
— 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;

——————————————————————————————
——————————————————————————————

The stored procedure displays three columns for each record: (i) Table Schema, (ii) Table Name, (iii) A sample query that can be executed and returns the top 10 rows for the specific table.

Sample output of the stored procedure:

Figure 1: Sample Output of the DBTableInfo Stored Procedure.

You can also download the stored procedure using the following link.