View List of tables in SQL 2005 and SQL 2008

Hi,

In one of our programs we have to view list of tables when the user selects a database. We were using the following SQL statement to retrieve tables list

select table_schema,table_name from information_schema.tables

But some users were reporting "Invalid object name information_schema" error. Our question is whether read permission on INFORMATION_SCHEMA is available to all the users by default?

Or shall we change the SQL statement to the following so that it will not give any error

select s.name,t.name from sys.tables t,sys.schemas s where t.schema_id=s.schema_id

Which one will be more efficient and will work by default in Ver. 2005 or Ver. 2008.

Thanks in advance for the answers.

Shah

asked Jul 22 '10 at 05:44 AM in Default

shah
1 ● 1 ● 1 ● 1

(comments are locked)

6 answers:

Which one to use is easy, information_schema. It's the one place the Microsoft has assured us won't change from version to version. And these are part of the ANSI 92 standard. It's been roughly the same since 7. That's 12+ years of consistency.

As far as the security goes, according to BOL on information_schema.tables:

Returns one row for each table in the current database for which the current user has permissions.

This suggests that you may need to grant 'VIEW DEFINITION' on databases where the user doesn't have any other sort of access. But since they don't have access to a database, they shouldn't be looking at it, right?

answered Jul 22 '10 at 05:54 AM

Grant Fritchey ♦♦
62.4k ● 12 ● 20 ● 66

(comments are locked)

Personally I'd go the other way, and query the system views if you're on 2005+. Basically because they tell you a lot more about things than the information_schema views do. For tables, for instance, you get the principal that owns them, the creation and modification dates, replication information, data space information for LOBs and FileStreams... The list goes on.

If you just want very basic access to a list of object names, sure, go for INFORMATION_SCHEMA views, but if you want to know anything about the extended features and operation of SQL Server, use the system views.

Just my 2 cents.

answered Jul 22 '10 at 07:38 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Make sure the database which are used is not case-sensitive, in general all INFORMATION_SCHEMA views are in upper case letters.

answered Jul 22 '10 at 05:50 AM

SQLMaster
81 ● 2

I wouldn't recommend using direct access to system catalogs, rather ISVs is a best practice.

Jul 22 '10 at 05:51 AM SQLMaster

(comments are locked)

Thanks SQLMaster and Grant Fritchey for your prompt answers.

When we are querying information_schema views and if a user does not have privilege they are getting errors. We want to avoid this. That's why we want to directly query the system views to avoid this.

Please advise whether all users will be able to query the system views or some will not have privilege and again they will get error?

Or is there any other fool proof way to retrieve list of tables along with schema names.

Please advice as we are new to SQL Server.

Thanks in advance!

answered Jul 22 '10 at 07:31 AM

shah
1 ● 1 ● 1 ● 1

People querying system views will generally get results, but only for things that they have permission to see.

Jul 22 '10 at 07:33 AM Matt Whitfield ♦♦

If the user does not have access to a database, they will not be able to query that database and retrieve tables, but that should be expected behavior, not a surprise.

Jul 22 '10 at 10:15 AM Grant Fritchey ♦♦

(comments are locked)