x

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
more ▼

asked Jul 22, 2010 at 05:44 AM in Default

shah gravatar image

shah
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

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?
more ▼

answered Jul 22, 2010 at 05:54 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 22, 2010 at 07:38 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Make sure the database which are used is not case-sensitive, in general all INFORMATION_SCHEMA views are in upper case letters.
more ▼

answered Jul 22, 2010 at 05:50 AM

SQLMaster gravatar image

SQLMaster
81 2

I wouldn't recommend using direct access to system catalogs, rather ISVs is a best practice.
Jul 22, 2010 at 05:51 AM SQLMaster
(comments are locked)
10|1200 characters needed characters left

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!

more ▼

answered Jul 22, 2010 at 07:31 AM

shah gravatar image

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, 2010 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, 2010 at 10:15 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

you can get the list of table in a database by using the Query

Select * from Sysobjects where xtype = 'U'
more ▼

answered Jul 23, 2010 at 01:32 PM

basit 1 gravatar image

basit 1
499 51 61 84

for SQL 2005 onwards, you should be accessing sys.objects or sys.tables instead of sysobjects.
Jul 25, 2010 at 01:13 AM Squirrel
correct...
Aug 04, 2010 at 01:14 AM basit 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x85
x6

asked: Jul 22, 2010 at 05:44 AM

Seen: 2397 times

Last Updated: Jul 25, 2010 at 04:46 AM