20140123

List Tables from ODBC

I was wanting to see the available catalogs on a linked server, but the catalogs branch was empty, so I thought I would try finding a query that would show them.

I'd done this before with TSQL on SQL Server:
SELECT * FROM sys.objects
or
SELECT * FROM sysobjects

But I had never done it for an ODBC linked server (this one happens to be Oracle)
After some conversations and searching I came up with this:
SELECT * FROM USER_TAB_PRIVS;
and for permissions:
SELECT * FROM USER_ROLE_PRIVS;

If you are also using a Linked Server you will need to use the OPENQUERY function
SELECT * FROM OPENQUERY([Linked Server Name], 'SELECT * FROM USER_TAB_PRIVS;')

You may even need to build the text and run it using sp_executesql:
DECLARE @Query nvarchar(max) = 'SELECT * FROM OPENQUERY([Linked Server Name], ''SELECT * FROM USER_TAB_PRIVS;'')'
PRINT @Query
exec sp_executesql @Query;

No comments:

Post a Comment