Understanding schemas

Question

Understanding schemas

michael.leach2015

SSCommitted

Points: 1597
More actions
August 2, 2019 at 10:54 pm

#3668421
I queried sys.tables, sys.all_views, sys.all_objects and .  The following appear to be views:
1.    tables
2.    all_views
3.    all_objects
In all 3 of these cases what is the ‘sys’ part? Isn’t that a schema?
I did a SELECT * on all_objects so I can view all objects. I have a few questions about the results of this query:
1. Does the “all_objects” view return all objects in the current database or all objects in all databases in my SSMS installation?
2. As far as I know a schema still counts as a database object right? If this is true and if the “sys” in all 3 cases above is the name of a schema, then why doesn’t the all_objects view list “sys” as an object?
3. I sorted by the ‘name’ column. I noticed there are two objects named “table.” They both have a different object ID. One is spelled in all lower case and the other is spelled in all caps. How could you have two objects of the same name? Is it because the casing is different (lower case vs. all upper case)? I thought I remembered reading somewhere that you can have two objects of the same name as long as they are in different schemas. Could that be it?
August 5, 2019 at 6:14 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

The documentation for the all_objects catalog view states that it shows all schema-scoped user-defined objects and system objects. Database-scoped objects that aren't within a specific schema are not included - this includes schemas themselves, database users, roles etc. The types of objects that are included are listed here.

You're correct about the names of schema-scoped objects (table, stored procedures, etc) being only unique within the same schema. I may be wrong on this, but I believe differences in casing doesn't affect uniqueness in the default non case-sensitive collation. Again, it's that schema_id that the names need to be unique within, and checking the sys.schemas view, the two tables objects you've referenced are in the "INFORMATION_SCHEMA" and "sys" schemas.

As an example of having two tables with the same name in the database, these statements execute just fine.

CREATE SCHEMA Costpoint;
GO
CREATE TABLE Costpoint.Employee (
employeeID INT PRIMARY KEY IDENTITY(1,1)
)
CREATE TABLE dbo.Employee (
employeeID INT PRIMARY KEY IDENTITY(1,1)
)

Best,

Andrew

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 2

Andrew,

What you said makes sense. Thank you for clarifying this. I noticed the SQL documentation mentioned 'schema scoped' but I wasn't sure what that meant. After reading your post I think I understand now.