Extract Objects of a Linked Server

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 = 'SPRI14U11011' --Name of Linked Server (as per sys.servers table)
Set @table_catalog = 'u_dbfrl001' --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 = 'SPRI14U11011',
@table_catalog = 'u_dbfrl001',
@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