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
But some users were reporting "Invalid object name information_schema" error. Our question is whether read permission on
Or shall we change the SQL statement to the following so that it will not give any error
Which one will be more efficient and will work by default in Ver. 2005 or Ver. 2008.
Thanks in advance for the 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:
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, 2010 at 05:54 AM
Grant Fritchey ♦♦
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, 2010 at 07:38 AM
Matt Whitfield ♦♦
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, 2010 at 05:50 AM
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, 2010 at 07:31 AM
you can get the list of table in a database by using the Query
Select * from Sysobjects where xtype = 'U'
answered Jul 23, 2010 at 01:32 PM