Technical Article

Extract Objects of a Linked Server

,

Update values of @table_server , @table_catalog and @table_varilables as per your requirement.

 

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

Read 669 times
(2 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating