CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2008-r2
bilal_izloo
bilal_izloo

Reputation: 37

Invalid Object Name Sys.objects

I am new to sql i am executing a query which return error `Invalid object name 'sys.objects'. I have the basic knowledge that columns tables,e.t.c is stored in system schema, if true than why i get this error.

SELECT * FROM sys.objects

Upvotes: 1

Views: 24677

Answers (4)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

This is most likely an issue of permissions. Starting in SQL Server 2005, all databases have a sys.objects system view so the query in the question is correct.

And this is not likely an issue of a case-sensitive database since the object names are in all lower case so even if case-sensitive, the query in the question is still correct. Of course, this assumes that the query in the question (all lower-case) and not the query in the title (mixed-case) is being used. As pointed out by @Sean in a comment on the question, a mixed-case system schema and/or object name will receive an "Invalid Object" error in a case-sensitive database.

I will try to find the exact permission(s), but either:

  • some basic permission has not been granted (i.e. membership in the public built-in database role)

    or

  • a DENY has been added somewhere in the permission chain

Well, interesting. So far all permissions attempts, including membership in the db_denydatareader built-in database role, result in the following (which at least acknowledges the existence of sys.objects):

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'.

Another option: what is the compatibility_level of the database set to? It is possible that it is set to 80 which corresponds to SQL Server 2000, which should have the effect of not knowing about sys.objects.

Upvotes: 0

user1633947
user1633947

Reputation: 149

I have SQL Server 2000 and using SELECT * FROM dbo.sysobjects worked for me. (do instead of sys)

Upvotes: 3

Dan Guzman
Dan Guzman

Reputation: 46203

Like many other DBMS systems, SQL Server exposes meta data using catalog views and functions. See http://msdn.microsoft.com/en-us/library/ms174365.aspx. sysobjects (actually dbo.sysobjects) was replaced by sys.objects in SQL Server 2005, with sysobjects provided for backwards compatibility.

Catalog views like sys.objects are often used to assert the expected state of the database before running DDL scripts.

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

The correct query is:

SELECT * FROM sys.sysobjects

because the first sys is schema, but the name of table is sysobjects and no objects

You can also write

SELECT * FROM sysobjects

without explicit the schema.

In sysobjects table you can read all objects present in your DB (you can find tables, views, SP and so on)

Upvotes: 4

Related Questions