ARTICLE

Schemas in SQL Server

Posted by Venkatesan Jayakantham Articles | SQL Server 2012 October 18, 2010
Schemas are nothing but logical grouping of objects either it may be a table or view or stored procedure or function or any other DB objects.
Reader Level:

Schemas are nothing but logical grouping of objects either it may be a table or view or stored procedure or function or any other DB objects.
 
For example,

Am having an enterprise wide database where you will be having HR related tables and Employee related tables. In this case, employees won't have access to the HR related tables. In that case, we can group the tables under one roof like employee tables can be placed in Employee schema and HR related tables can be placed under HR schema.
 
Right click on the Your Database ->  Security -> Schemas -> New Schema

1.gif 
 
You will get the new schema option, provide the schema name and the schema owner under General tab.

2.gif

Under permissions tab, you can select the users who will come under this schema and also you can provide the type of access for each user under this schema.

3.gif
  
You can create your objects under the schema using the below query. (SchemaName.ObjectName)
 
CREATE TABLE venkatSchema.venkat(ID INT)
 
Accessing the objects can be done as Schemaname.ObjectName
 
SELECT * FROM venkatSchema.venkat

Login to add your contents and source code to this article
post comment
     
COMMENT USING
PREMIUM SPONSORS
DynamicPDF™ product line allows you to dynamically generate PDF documents, merge PDF documents and add new content to existing PDF documents from within your applications.
Join a Chapter
SPONSORED BY
  • PDF reports have never been easier to create. With our included WYSIWYG Designer, you can layout your reports, set up your data source and let DynamicPDF ReportWriter do the rest.
Get Career Advice from Experts