Get table,view,store procedures list from linked server.

By
Linked Server

Dear Code How to get table,view ,store procedures list from linked server?I am asking this because i create a linked server in SQL Server and i don't remember the table list of linked server.
Dear Topic Yes i have the query for this.When we use the Linked Server then if execute 'select * from sys.tables' then its list all tables of database in which we are working.I have two type of query to get the list of tables from linked server.

Linked Server Sql Server Query


--first approch
sp_tables_ex
@table_server = 'linkedServer-Name',
@table_catalog='linkedServer-Database-Name',
@table_schema='linkedServer-Schema-Name'

--In @table_server name if you set you current server name then its give all table and view from current server 
--second approch

SELECT * FROM <'serverName'>.<databasename>.sys.objects where type ='u'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='u'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='p'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='vw'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='fn'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='tf'

SELECT * FROM [xx.xxx.xxx.xxx].databasebname.sys.objects where type ='tr'

Output of linked server connection

Output looks like below.

Output of first approach query.


Output of second approach query.

Explanation

With first approach we are not able to get function or store procedure name.But with second approach when type ='u' then its list all table name.if 'p' then all store procedure name ,'fn' list all scaler valued function name,'tf'' list all tabular function .In first approach used sp_tables_ex. if we check with sp_helptext sp_tables_ex then its return the definition and when we check its definition  then its takes more than above passed parameter.In above query i pass only 3 parameter you can check more himself. In second approach we are familiar with all query and i already explain this in my previous topic.

0 comments:

Post a Comment