Post reply

schema name of a table from remote server

  • May 27, 2009 at 6:12 am

    #131946

    Hi Friends,

    Is it possible for us to get the schema name of a table from a remote server.

    Actually , i ahd been working on distribution transaction and i fire the query

    select @returnp = table_owner from openquery(' + @srclnkserver + ', ''exec sp_tables'') where table_name ='XXXXX'

    but i get transactiion context in use in another session.

    So are there any other ways of getting the schema name from remote server.

    Thanks,

  • May 27, 2009 at 6:35 am

    #1000520

    You can use sp_tables_ex procedure to execute distributed queries or use system objects like "INFORMATION_SCHEMA.TABLES" or "sys.objects" in conjunction with "sys.schemas".

    Here are few ways of doing it.

    SELECT*

    FROMOPENQUERY( TestServer, 'SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES' )

    WHERETABLE_NAME ='XXXXX'

    SELECT*

    FROMOPENQUERY( TestServer, 'SELECT o.name as , s.name as [schema] FROM sys.objects o inner join sys.schemas s on o.schema_id = s.schema_id' )

    WHERE ='XXXXX'

    EXECUTEsp_tables_ex @table_server = 'TestServer',

    @table_catalog = 'SomeDB',

    @table_name = 'XXXXX',

    @table_type = 'TABLE'

    --Ramesh

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply