sql schema

• kalrpuli

  SSC-Addicted

  Points: 429

  More actions

  September 17, 2010 at 12:10 am

  #240506

  what is sqlschema in sqlserver?

• Arjun SreeVastsva

  SSCertifiable

  Points: 7150

  More actions

  September 17, 2010 at 12:30 am

  #1222754

  A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.

  Properties

  * Ownership of schemas and schema-scoped securables is transferable.

  * Objects can be moved between schemas

  * A single schema can contain objects owned by multiple database users.

  * Multiple database users can share a single default schema.

  * Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.

  * A schema can be owned by any database principal. This includes roles and application roles.

  * A database user can be dropped without dropping objects in a corresponding schema.

  Create database SQL2k5

  Use SQL2k5

  – Created Schema Employee –

  Create Schema Employee

  – Created table in Employee schema –

  Create Table Employee.EmpInfo

  (

  EmpNo int Primary Key identity(1,1),

  EmpName varchar(20)

  )

  – data insertion –

  Insert Into Employee.Empinfo Values(‘Jshah-3')

  – Data Selection –

  Select * From Employee.Empinfo

  – Created another schema HR –

  Create Schema HR

  – Transfer Objects between Schemas –

  ALTER SCHEMA HR

  TRANSFER Employee.Empinfo

  – Assigning Permission to Schema –

  GRANT SELECT ON SCHEMA::HR TO Jshah

• Venkataraman R

  Say Hey Kid

  Points: 694

  More actions

  September 17, 2010 at 2:59 am

  #1222822

  Schema is container of database objects aka Securables like Entity,View, Stored procedure etc. It is similar to namespace in programming languages. It can also be correlated with a bank locker, where you can put all your securables like Jewellery, Costly Items etc.

  Eg. Person.Address entity. Here Person refers to Schema and Address refers to entity.

  A Schema can have single owner at a time. Ownership of Schema can be transferred to another user using Authorization. Schema and user are differentiated from SQL Server 2005 onwards. A single schema can contain objects of more than one user. A user can have ownership of more than one schema. Securables can be moved across schemas. A user can have one default schema associated with him.

  Advantages of Schema:

  * Brings lot of logical grouping to the database objects.

  * More Structured way to manage the Permissions of database objects

• Paul White

  SSC Guru

  Points: 150468

  More actions

  September 17, 2010 at 3:39 am

  #1222847

  http://en.wikipedia.org/wiki/Database_schema

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi