Skip to main content

What is meant by Database Schema in MS SQL

Ever since SQL Server 2005 was released, each object in a database has belonged to a database schema. SQL Server 2008 has continued with database schemas, and an explanation follows.

What is a Database Schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.
You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access.
Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Creating a Database Schema

To create a database schema in SQL Server 2008:
  1. Navigate to Security > Schemas
  2. Right click on Schemas and select New Schema.... Like this:
    Creating a database schema in SQL Server Management Studio
  3. Complete the details in the General tab for the new schema. In this example, the schema name is "person" and the schema owner is "Homer".
    Creating a database schema in SQL Server Management Studio
  4. Add users to the schema as required and set their permissions:
    Creating a database schema in SQL Server Management Studio
  5. Add any extended properties (via the Extended Properties tab)
  6. Click OK.

Add a Table to the New Schema

Now that we have a new schema, we can add objects such as tables, views, and stored procedures to it. For example, we could transfer the table that we created in the earlier lesson to the new schema.
When we created that table (called "Individual"), it was created in the default database schema ("dbo"). We know this because it appears in our object browser as "dbo.Individual".
To transfer the "Individual" table to the person "schema":
  1. In Object Explorer, right click on the table name and select "Design":
    Changing database schema for a table in SQL Server Management Studio
  2. From Design view, press F4 to display the Properties window.
  3. From the Properties window, change the schema to the desired schema:
    Changing database schema for a table in SQL Server Management Studio
  4. Close Design View by right clicking the tab and selecting "Close":
    Closing Design View
  5. Click "OK" when prompted to save
Your table has now been transferred to the "person" schema.

Confirm your Change

To confirm the change:
  1. Refresh the Object Browser view:
    Refreshing the view in Object Browser
  2. You will now see that Object Browser displays the new schema for the table (person.Individual):
    Screenshot of the table in Object Browser
Labels:

Comments

Post a Comment

Popular posts from this blog

How to make the email Address Dummy in Oracle Database?

How to make the email Address Dummy in Oracle Database? Query to make the email Address as dummy, update <tablename> set <Columnname> = '@' || <Columnname> where <Columnname>  is not null; Query to Remove the email Address as dummy, update <tablename> set <Columnname>= substr(<Columnname>,2,length(<Columnname>)) where <Columnname> is not null;
Post a Comment
Read more

ORA-06550, WMSYS.LT_EXPORT_PKG.schema_info_exp

When you do full database export it fails will following errors as below. Export: Release 11.2.0.1.0 - Production on Mon Sep 30 12:04:38 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA schemas=user1 directory=data_pump_dir dumpfile=user1.dmp logfile=user1.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE ORA-39127: unexpected error from call to export_string :=WMSYS.LT_EXPORT_PKG.schema_info_exp('user1...
2 comments
Read more

How to clear screen in sqlplus

If you want to clear screen in sqlplus window there is small cmd that you need to remember which is below, SQL> select username from dba_users; USERNAME -------------------- BOCMS BOAUDIT  CKI8727 GRU9151 MNA9189 FRE9283  KKO8750  KMU8649 HME9041 ZLP1492 DBE9176  QRA8651  PFI8706 SQL> host cls Once you hit enter the screen will be cleared. SQL> select username from dba_users; USERNAME -------------------- BOCMS BOAUDIT  CKI8727 GRU9151 MNA9189 FRE9283  KKO8750  KMU8649 HME9041 ZLP1492 DBE9176  QRA8651  PFI8706 SQL>clear screen Once you hit enter the screen will be cleared.
Post a Comment
Read more