Script to extract objects available on a Linked Server

Hi,

In a scenario where we need to find out all the objects of a LINKED Server and check which object is accessible to us, we need to write following query:

Declare @min int,@max int
Declare @sql varchar(100)
Declare @table_server varchar(100)
Declare @table_catalog varchar(100)
Declare @table_schema varchar(100)
Declare @tablen varchar(100)


Set @table_server = 'LINKEDSERVER' --Name of Linked Server (as per sys.servers table)
Set @table_catalog = 'DATABASENAME' --Database Name
Set @table_schema = 'dbo' --Database owner

--Now create a table variable with identity column to help loop through.

Declare @table table
(Id int identity(1,1),
table_cat varchar(100),
Table_schem varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100))

--Insert data into table variable
Insert into @table
exec sp_tables_ex --Use sp_tables_ex procedure
@table_server = 'LINKEDSERVER',
@table_catalog = 'DATABASENAME',
@table_schema = 'dbo'

select * from @table --Check if anything is populated
set nocount on

--As this populate, remove SYSTEM TABLES
Delete from @table where Table_Type = 'SYSTEM TABLE' 

--Extract Min & Max value to run loop on the table variable.
Select @min = min(id), @max=max(id) from @table

while (@min<=@max)
Begin

--Extract Table name to refer from @table table variable.
Select @tablen = Table_Name from @table where id = @min

--Prepare dynamic query
Set @sql = 'Select top 10 * from ' + @table_server + '.' + @table_catalog + '.' + @table_schema + '.' + @tablen

print @sql
begin try
--Execute dynamic query in TRY block to check for permission
exec(@sql)
end try
begin catch
--If come here then we know this query is not accessible.
print @sql + ' not worked'
End catch
set @min = @min + 1 --Increment loop
End

Let me know if you have any question.

Regards,

Happy SQL Coding