Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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!
0
itfocus
Asked:
itfocus
  • 3
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
Sure sounds like a potential permissions issue.

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.
0
 
itfocusAuthor Commented:
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?
0
 
Scott PletcherSenior DBACommented:
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherSenior DBACommented:
Maybe you created the other schemas in another db by accident?

Otherwise this just makes no sense.
0
 
itfocusAuthor Commented:
Hi,

All the schemas were on the right DB and everything; I think it may have been something weird in the setup of the database though (it's an old, test one).  I tried the same thing on a couple of others, and it works ok, so will just chalk it up to random weirdness and move on.

Thanks for the feedback though.
0
 
itfocusAuthor Commented:
My own comment upon closing the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now