Tag: Schema Properties

  • SQL Server 2005 Schema, Schema Properties, Schema T-SQL

    What is Schema in SQL Server 2005? Explain its properties with example?

    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