Objects and schema scoping

Most common objects in SQL Server are schema-scoped, that is they belong to a schema. In SQL Server 2000, schemas and database users were effectively the same thing, so objects could be called ‘user-scoped’. However, starting with SQL Server 2005 schemas and users (database principals) were separated. This means that schemas are now no longer tied directly to the users in the database.

So, schema scoped objects are those that belong directly to a schema. A schema can be thought of as a logical partition within a database which directly serves two purposes:

• To separate logical areas of the database such that users have a clear security constraints within your databases. understanding of what objects interact with which other objects. This can make your database a lot easier to understand for new people coming in and trying to understand your schemas. When you need to hire help, it’s probably because you are busy. Do you want to spend your time explaining your schema to your new help? Probably not so much.


• To enable security control to be applied to those logical groups to individual database users. This makes it both more convenient to apply permissions to logical groups of objects, and easier to understand the security picture in your databases.

Schema scoped objects can be found generally in the [sys].[objects] table. This table contains the following object types:

Aggregate function (CLR)
CHECK constraint
DEFAULT (constraint or stand-alone)
FOREIGN KEY constraint
SQL scalar function
Assembly (CLR) scalar-function
Assembly (CLR) table-valued function
SQL inline table-valued function
SQL Stored Procedure
Assembly (CLR) stored-procedure
PRIMARY KEY constraint
Rule (old-style, stand-alone)
Replication-filter-procedure
Synonym
Service queue
Assembly (CLR) DML trigger
SQL table-valued-function
SQL DML trigger
Table type
Table (user-defined)
UNIQUE constraint
View
Extended stored procedure

In this list, some of the object types are directly schema-scoped (i.e. they have a schema because you have chosen to place them in a schema for a reason) and some are indirectly schema-scoped (i.e. they have a schema because they are a child object of another object that belongs to a schema). While indirectly schema-scoped objects belong to their schema, they cannot have a schema different to that of their parent object.

So, the directly schema scoped objects in [sys].[objects] are:

Aggregate function (CLR)
Assembly (CLR) scalar-function
SQL scalar function
Assembly (CLR) table-valued function
SQL table-valued-function
SQL inline table-valued function
Assembly (CLR) stored-procedure
SQL Stored Procedure
Default (old-style, stand-alone)
Rule (old-style, stand-alone)
Replication-filter-procedure
Synonym
Service queue
Table (user-defined)
View
Table type
Extended stored procedure

And the indirectly schema scoped objects in [sys].[objects] are:

CHECK constraint
DEFAULT constraint
FOREIGN KEY constraint
PRIMARY KEY constraint
Assembly (CLR) DML trigger
SQL DML trigger
UNIQUE constraint

However, there are other object types which are schema-scoped that are not present in [sys].[objects]. Hang on, doesn’t the MSDN description for [sys].[objects] say:

Contains a row for each user-defined, schema-scoped object that is created within a database.

Yes it does, but XML Schema Collections, user defined SQL types and user defined CLR types are all schema-scoped, yet do not appear in [sys].[objects]. Hey, that’s life (and also why I had a hard time writing my schema comparison tool). In the next article, I will explain what the columns in [sys].[objects] mean, and begin to explain the meta-data for the various supplementary system views that provide extra information that is relevant to each different type.