SQL Server Schema Basics
Previously I touched on how SQL Server Schema can be of immense use. I think its best if I cover some more content on same topic:
What is SQL Server Schema?
SQL Server schema is a container of database objects and follows following rules:
- May contain different object types, like tables, indexes, views, procedures, functions, etc.
- A database user can be assigned with a default schema. Every schema has a single owner (a database user). A database user may own multiple schemas.
- Object names must be prefixed with schema names when referencing schemas outside user’s default schema.
- If your login name is mapped to the owner of a schema, you have full permissions on this schema.
- To drop a schema, you need to empty the schema first.
How to create a new Schema?
USE <DBName>
GO
CREATE SCHEMA <yourSchemaName>
GO
How to list all schema in a Database?
SELECT * FROM sys.schemas;
How to create table in a given schema?
CREATE TABLE <yourSchemaName>.testTable ( id INT);
GO
How to transfer objects (table in our example) from one schema to other?
– Create the needed schema and test object
CREATE SCHEMA test1
GO
CREATE SCHEMA test2
GO
CREATE TABLE test1.testTable(ID INT)
GO
– Validate for test object’s current Schema
SELECT t.name AS table_name, t.type_desc,
s.name AS schema_name
FROM sys.tables t, sys.schemas s
WHERE t.schema_id = s.schema_id
and t.name like ‘%test%’
– Transfer the object to new schema
ALTER SCHEMA test2 transfer test1.testTable
– Validate for test object’s current Schema
SELECT t.name AS table_name, t.type_desc,
s.name AS schema_name
FROM sys.tables t, sys.schemas s
WHERE t.schema_id = s.schema_id
and t.name like ‘%test%’
How to list objects within a Schema?
USEAdventureWorks
GO
Select o.name, o.schema_id, o.type_desc
FROM sys.objects o, sys.schemas s
WHERE o.schema_id = s.schema_id
AND s.name = ‘HumanResources’;
– HumanResources is the schema name
How to create an User account and assigning Default Schema?
USE [master]
GO
CREATE LOGIN [testUser] WITH PASSWORD=N’testUser’, DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [AdventureWorks]
GO
CREATE USER [testUser] FOR LOGIN [testUser]
GO
ALTER USER [testUser] WITH DEFAULT_SCHEMA=[test2]
GO
EXEC sp_addrolememberN’db_owner’, N’vikas’
GO
How to check default Schema of your Login?
select name, default_schema_name
FROM sys.database_principals WHERE type = ‘S’;
How to check ownership of specific Schema?
SELECT s.name, u.name AS owner
FROM sys.schemas s, sys.database_principals u
WHERE s.principal_id = u.principal_id
AND s.name like ‘%test%’;
– where %test% is the schema name patter
How to change ownership of a schema?
ALTER AUTHORIZATION ON SCHEMA::test1 TO testUser
How to drop a schema?
DROP SCHEMA testUsertest1
– you might receive an error if the schema is not empty. In such case either drop or transfer the objects to other schema.
