SQL Server sys.objects not showing Schemas
Hi,
I was always under the impression that "select * from sys.objects" should return every object in the database, but I am finding it is only returning objects under the main dbo schema. There are three other schemas in the database, which show up when you query sys.schemas, but they are just missing from the main objects list. I have tried various INFORMATION_SCHEMA items (tables, procedures, views etc) too, but the same thing is happening.
I am logged in as a user with full access to all the schemas (has equivalent sa rights), so I don't think it's permissions.
What am I missing? Presumably there must be a way to return every single object in the database, regardless of the schema? (this is for a report, so I need to include everything).
Many thanks!
I was always under the impression that "select * from sys.objects" should return every object in the database, but I am finding it is only returning objects under the main dbo schema. There are three other schemas in the database, which show up when you query sys.schemas, but they are just missing from the main objects list. I have tried various INFORMATION_SCHEMA items (tables, procedures, views etc) too, but the same thing is happening.
I am logged in as a user with full access to all the schemas (has equivalent sa rights), so I don't think it's permissions.
What am I missing? Presumably there must be a way to return every single object in the database, regardless of the schema? (this is for a report, so I need to include everything).
Many thanks!
ASKER
I have just double-checked, and the user does indeed have sysadmin authority. I added serveradmin authority as well, just to see if that made a difference. I am now seeing the dbo and the sys objects, but again, not the custom-created ones. I had a look at one of the schemas, to see if I had to set permissions to this user specifically, but it didn't make any difference.
I reran "select * from sys.schemas" but actually looking at it again, none of the custom schemas are listed here either. all the db_*** ones are, but my user-created ones are not. Am I just looking in the wrong place?
I reran "select * from sys.schemas" but actually looking at it again, none of the custom schemas are listed here either. all the db_*** ones are, but my user-created ones are not. Am I just looking in the wrong place?
Yeah, make sure you're in the right db.
And that the other names aren't synonyms or some other name that would not be in that db's sys.objects table -- although the synonyms themselves should be in sys.objects I think.
And that the other names aren't synonyms or some other name that would not be in that db's sys.objects table -- although the synonyms themselves should be in sys.objects I think.
Maybe you created the other schemas in another db by accident?
Otherwise this just makes no sense.
Otherwise this just makes no sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My own comment upon closing the question.
If the object is schema-scoped, it should show up in sys.objects.
Does the login have actual server-level "sysadmin"authority? If so, it's not a permissions issue, and something else is going on.