Transact SQL – Schema – Operations

Background

Wanted to document a couple of operations one can take on schemas.

Outline

  1. Check Existence
  2. Create Schema
  3. Change Object’s Schema ( Transfer Object from one schema to another)
  4. Grant Permission

Action

Exist?

Syntax

schema_id()

Sample

set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int

set @schema = 'security';

set @schemaID = schema_id(@schema);

if ( @schemaID is null )
begin

	print 'Schema ' + @schema + ' does not exist!'

end
else
begin

	print 'Schema ' + @schema + ' exists'

end

Create

Syntax

create schema [schema] authorization [owner];

Sample

if schema_id('security') is null
begin

	exec('create schema [security] authorization [dbo]');

end

Change Object Schema

Syntax


alter schema [schema-new]
   transfer [scheme-current].[object-current]

Sample


set nocount on;
go

set XACT_ABORT on;
go

declare @object sysname
declare @commit bit

set @object = 'permission';

set @commit = 0

if schema_id('security') is null
begin

    exec('create schema [security] authorization [dbo]');

end

begin tran

    --exec sp_help '[dbo].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

    if object_id('[dbo].[permission]') is not null
    begin

        alter schema [security]
            transfer [dbo].[permission]

    end	

    -- exec sp_help '[security].[permission]'

    select
              [schema] = tblSS.[name]
            , [object] = tblSO.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.schema_id = tblSS.schema_id

    where  tblSO.[type] = 'U'

    and    tblSO.[name] = @object

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Grant Permissions

Syntax


    grant [permission] on [schema]::[schema-name] to [database-principal];

Sample


set nocount on
go

set XACT_ABORT on
go

declare @schema sysname
declare @schemaID int
declare @commit   bit

set @commit = 0

set @schema = 'security';

set @schemaID = schema_id(@schema);

begin tran

	if ( @schemaID is null )
	begin

		print 'Schema ' + @schema + ' does not exist!'

	end
	else
	begin

		print 'Schema ' + @schema + ' grant permission'

		grant select on schema::[security] to [public];

	end

while (@@trancount > 0)
begin

    if (@commit = 1)
    begin

        print 'commit  tran'
        commit tran;

    end
    else
    begin

        print 'rollback tran'
        rollback tran;

    end

end

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s