Schema

A schema is also the container that owns all objects within a database.

CREATE SCHEMA < schema name> AUTHORIZATION <owner name >

Returning Name of Default Schema

SELECT SCHEMA_NAME ();

dbo

Returning Name of  Schema by using ID

SELECT SCHEMA_NAME ( 3);

INFORMATION_SCHEMA

  

How to Find All Schemas in SQL SERVER ?

SELECT name FROM sys. schemas

SELECT SCHEMA_NAME FROM IN  FORMATION_SCHEMA.SCHEMATA (Preferred)

INFORMATION_SCHEMA views is recommended as they protect you from changes to the underlying sys tables. From the SQL Server 2008 R2 Help:

Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.

 Advantages Of Using Schemas

Apart from the obvious benefit that objects can now be manipulated independently of users, usage of schemas also offers the following advantages:

Managing logical entities in one physical database: Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases. Schemas help to manage the logical entities separately from one another, but still allow objects to work together where required

Conclusion

Schemas, introduced in SQL Server 2005, offer a convenient way to separate database users from database object owners. They give DBA’s the ability to protect sensitive objects in the database, and also to group logical entities together.

References

 https://msdn.microsoft.com/en-us/library/dd207005.aspx

www.quackit.com/sql_server/sql_server_2008/.../sql_server_database_sc...

·               

  (http://blog.sqlauthority.com),