How to read database and table metadata from MS SQL server with minimum permissions

Question

I want to come up with the minimal set of queries that return the databases and tables in a Microsoft SQL Server instance, on as many versions of SQL Server as possible.

I'm not sure if I should half-answer my own question, but here's what I think I need for 2000 and 2005. Ideally I'd go back further, but I don't have access to older versions:

Permissions

2005: a user with VIEW ANY DEFINITION permission

2000: a user the with public role on all databases to be retrieved

Databases

sp_databases

or

SELECT * FROM sysdatabases

both work on SQL Server 2000 and 2005

Tables

2005

SELECT name FROM <database>.sys.tables

or

SELECT table_name FROM <database>.information_schema.tables WHERE table_type = 'BASE TABLE'

2000

SELECT name from <database>.dbo.sysobjects WHERE xtype = 'U'

Answer 1

I belive INFORMATION_SCHEMA Views could help you.

http://msdn.microsoft.com/en-us/library/ms186778.aspx