Что такое схема в sql server

Create Schema, Database

Схема Schema с точки зрения базы данных представляет собой контейнер объектов типа таблиц, триггеров, хранимых процедур и т.п. В данной статье будут рассмотрены вопросы создания и удаления схемы БД следующих СУБД :

• Oracle : Schema привязывается к пользователю, т.е. наименование схемы, как правило, является учетной записью пользователя. Схема создается при создании пользователем первого объекта, и все последующие объекты созданные этим пользователем становятся частью этой схемы. Кроме этого Oracle позволяет создавать схему как контейнер одновременно с объектами базы данных.
• MSSQL : в Microsoft SQL Server начиная с версии 2005 жесткая связь между пользователями и схемами была отменена. Пользователи могут получить доступ на выполнение определенных операций с объектами схемы : чтение, запись, обновление или выполнение.
• PostgreSQL : Schema создается внутри объекта базы данных. Сервер может управлять несколькими базами данных, каждая из которых может включать несколько схем. То есть, как и в MSSQL, схема не связана с учетной записью пользователя.
• MySQL : понятие Schema имеет тождественный смысл с Database. База данных Database является контейнером объектов, к которым пользователь получает доступ.
• Derby : Schema не имеет жесткой связи с пользователем и является контейнером объектов, для доступа к которым пользователь должен иметь соответствующие привилегии.

Создание схемы, CREATE SCHEMA

Для создания схемы необходимо использовать SQL скрипт CREATE SCHEMA. Разные схемы могут включать одноименные объекты. При обращении к объектам разных схем необходимо указывать наименование схемы как префикс. Для создания схемы пользователь должен иметь соответствующие привилегии. Конечно же, superuser’ы данной привилегией владеют.

Создание схемы Oracle

Oracle относится к тем платформам СУБД, которые не имеют явной поддержки команды CREATE SCHEMA. Однако он все же неявно создаёт схему, когда пользователь создаёт свой первый объект базы данных. Данная СУБД использует команду «CREATE SCHEMA» для создания за одну транзакцию таблиц и представлений вместе с предоставлением доступа к ним.

Необходимо отметить, что Oracle разрешает дополнительно использовать в инструкции CREATE SCHEMA стандартные скрипты CREATE TABLE, CREATE VIEW и GRANT. Нельзя использовать любые расширения этих команд, имеющиеся в Oracle, если эти команды включены в инструкцию CREATE SCHEMA. Синтаксис создания объектов со схемой.

CREATE SCHEMA AUTHORIZATION schema options CREATE TABLE CREATE VIEW GRANT

В следующем примере для схемы «painter»» создаются таблица и представление. Коме этого в инструкции CREATE SCHEMA определен доступ к объектам.

CREATE SCHEMA AUTHORIZATION painter CREATE TABLE paint ( paint_id NUMBER PRIMARY KEY, paint_size NUMBER, colour VARCHAR2(10) ) CREATE VIEW large_paints AS SELECT paint_id, colour FROM paint WHERE paint_size = 100 GRANT select ON large_paints TO scott;

Порядок команд создания объектов и предоставления прав доступа в инструкции CREATE SCHEMA не критичен, но все же следует соблюдать синтаксис. Oracle выполняет инструкцию CREATE SCHEMA только в том случае, если все входящие в нее инструкции CREATE и GRANT были выполнены успешно.

Создание схемы MS SQL

В СУБД MS SQL при помощи транзакции CREATE SCHEMA можно создать схему одновременно с созданием в ней таблиц, представлений и предоставить или запретить доступ на эти объекты с использованием операторов GRANT, DENY или REVOKE.

CREATE SCHEMA schema_name_clause [ [ . n ] ] ::= < schema_name | AUTHORIZATION owner_name | schema_name AUTHORIZATION owner_name > ::=

• schema_name
  Идентификационного наименование схемы.
• AUTHORIZATION owner_name
  Учетная запись пользователя базы данных, который является владельцем схемы.
• table_definition
  SQL скрипт создания таблицы внутри схемы. Пользователь должен иметь права CREATE TABLE в текущей базе данных.
• view_definition
  SQL скрипт создания представления внутри схемы. Пользователь должен иметь права CREATE VIEW в текущей базе данных.
• grant_statement
  Предоставление разрешения на любой объект, за исключением новой схемы.
• revoke_statement
  Отмена разрешения на любой объект, за исключением новой схемы.
• deny_statement
  Запрещение разрешения на любой объект, за исключением новой схемы.

Транзакция CREATE SCHEMA являются атомарной. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, то ни один из указанных объектов не создается и ни одно разрешение не предоставляется.

Объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.

При помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.

Создание схемы PostgreSQL

Новая схема создается в текущей базе данных сервера, с которым установлено соединение. Наименование схемы должно быть уникально для данной Database.

CREATE SCHEMA schema_name [ AUTHORIZATION username ] [ schema_element [ . ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ . ] ]

• username
  имя пользователя (role name) владельца схемы. Если username отсутствует, то будет использоваться имя пользователя, исполняющего данный скрипт. Чтобы создать схему для владельца с другой ролью (role) необходимо иметь права данной роли или быть superuser’ом.
• schema_element
  определяет SQL скрипт одновременного создания внутри схемы объектов. Вместе со схемой можно создать TABLE, VIEW, INDEX, SEQUENCE, TRIGGER с получением на них соответствующих прав. Если в скрипте присутствует AUTHORIZATION username то все права будут принадлежать username. После создания схемы можно также создать в ней другие объекты.

Примеры создания схемы в PostgreSQL :

CREATE SCHEMA orders; CREATE SCHEMA AUTHORIZATION alex; CREATE SCHEMA customers; CREATE TABLE customers.companies (id name not null, name varchar(64) not null, address varchar(128), registered date); CREATE VIEW customers.v_companies AS SELECT id, name, address FROM customers.companies WHERE address IS NOT NULL;

Примечание : Согласно SQL стандарту, владелец схемы всегда является «хозяином» всех находящихся внутри объектов. PostgreSQL, также как и MSSQL, разрешает создание внутри схем объектов, «хозяином» которых может быть не владелец схемы, но имеющий соответствующие привилегии данной схемы.

Создание базы данных MySQL

В MySQL понятие базы данных database равносильно понятию schema в других СУБД типа MSSQL, PostgreSQL, Derby и имеет тот же смысл — контейнер не привязанных к пользователю объектов. Создание базы данных выполняется с помощью оператора CREATE DATABASE.

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset] [COLLATE collation];

• db_name
  Имя создаваемой базы данных.
• IF NOT EXISTS
  Если данный паарметр не указать, то при создании базы данных с уже существующим именем, возникнет ошибка выполнения команды.
• CHARACTER SET
  Определение кодировки таблиц базы данных.
• COLLATE
  Определение порядка сортировки данных.

Если при создании таблицы эти параметры CHARACTER SET и COLLATE не указываются, то кодировка и порядок сортировки вновь создаваемой таблицы берутся из значений, указанных для текущей базы данных.

Примеры использования CREATE DATABASE

CREATE DATABASE forum CREATE DATABASE forum CHARACTER SET utf8 COLLATE utf8_general_ci;

Создание схемы Derby

CREATE SCHEMA

Наименование схемы не должно содержать более 128 символов и быть уникальным внутри базы данных. Также наименование не должно начинаться с префикса SYS.

Только владелец базы данных может создавать схему с наименованием, отличным от имени/логина пользователя, и только владелец базы данных может определять AUTHORIZATION username с именем/логином пользователя, отличным от текущего логина.

Примечание : username может принадлежать только пользователю, а не role.

CREATE SCHEMA CUSTOMERS AUTHORIZATION serg; CREATE SCHEMA AUTHORIZATION loran;

Удаление схемы, DROP SCHEMA

Для удаления схемы необходимо использовать SQL скрипт drop schema.

Удаление схемы Oracle

Для удаление схемы СУБД Oracle необходимо удалить пользователя; объекты схемы удаляются автоматически :

DROP USER user_name [CASCADE];

Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем) объекты.

Удаление схемы MSSQL

DROP SCHEMA schema_name

Удаляемая схема не должна содержать никаких объектов. Если схема содержит объекты, выполнение инструкции DROP заканчивается сбоем. Сведения о схемах можно увидеть в представлении каталога sys.schemas.

Удаление схемы PostgreSQL

Схема может быть удалена только её владельцем или superuser’ом. Необходимо помнить, что владелец owner может удалить схему и все содержащиеся в ней объекты даже если они ему не принадлежат.

При удалении схемы в PostgreSQL можно дополнительно включить параметры :

DROP SCHEMA [ IF EXISTS ] schema_name [, . ] [ CASCADE | RESTRICT ]

• IF EXISTS
  Проверка наличия схемы. Если схемы нет, то исключительная ситуация не возникнет.
• CASCADE
  Автоматически удалять объекты, содержащиеся в схеме.
• RESTRICT
  Не удалять схему, если она содержит объекты. Этот параметр используется по умолчанию.

Пример удаления схемы orders вместе с содержащимися в ней объектами :

DROP SCHEMA orders CASCADE;

Удаление базы данных MySQL

В СУБД MySQL удалить можно не только пустую базу данных.

DROP DATABASE [IF EXISTS] db_name

Если не указать параметр IF EXISTS, то при попытке удаления не существующей базы данных, возникнет ошибка выполнения команды. Данный параметр доступен в MySQL 3.22 и более поздних версиях. При выполнении команды DROP DATABASE удаляется как сама база данных, так и все объекты, которые в ней находятся.

В следующем примере удаляется база данных «forum» :

DROP DATABASE forum

Удаление схемы Derby

В СУБД Derby удалить можно только пустую схему. Схемы SYS и APP (схема пользователя по умолчанию) не могут быть удалены.

DROP SCHEMA schema_name RESTRICT;

Ключевое слово RESTRICT является обязательным и обязывает выполнение проверки наличия объектов в удаляемой схеме.

Обновление схемы, ALTER SCHEMA

В SQL стандарте скрипт ALTER SCHEMA не определен.

В PostgreSQL владельца или наименование схемы можно изменить скриптом ALTER SCHEMA.

ALTER SCHEMA name OWNER TO newowner; ALTER SCHEMA name RENAME TO newname;

Чтобы использовать ALTER SCHEMA необходимо быть владельцем схемы и иметь соответствующие привилегии. При изменении наименования схемы нужно иметь привилегии CREATE для текущей базы данных. Чтобы сменить владельца, необходимо быть членом соответствующей роли и иметь в ней привилегии CREATE.

В СУБД MSSQL с помощью скрипта ALTER SCHEMA можно перенести объекты из одной схемы в другую.

ALTER SCHEMA schema_name TRANSFER [ :: ] securable_name [;] ::=

Пользователи и схемы в MSSQL полностью разделены. Инструкция ALTER SCHEMA применяется только для перемещения объектов между схемами в пределах одной базы данных. В следующем примере схема Customers изменяется путем перемещения в нее таблицы Cities из схемы Persons.

ALTER SCHEMA Customers TRANSFER Persons.Cities;

Что такое схема в sql server

PostgreSQL и Microsoft SQL Server поддерживают подключение к произвольной схеме внутри базы данных. По умолчанию на PostgreSQL используется схема public , на SQL Server — схема dbo .

PostgreSQL

Для использования произвольной схемы на PostgreSQL укажите параметр currentSchema в свойстве connectionParams задач сборки createDb и updateDb, например:

task createDb(dependsOn: assembleDbScripts, type: CubaDbCreation) < dbms = 'postgres' host = 'localhost' dbName = 'my_db' connectionParams = '?currentSchema=my_schema' dbUser = 'cuba' dbPassword = 'cuba' >

При использовании Studio, добавьте этот параметр в поле Connection params страницы Project properties. При этом Studio автоматически обновит build.gradle . После этого можно запускать обновление или пересоздание БД, все таблицы будут созданы в указанной схеме.

Microsoft SQL Server

На Microsoft SQL Server указания параметра подключения недостаточно, необходимо также создать связь между пользователем БД и схемой. Ниже приведен пример создания базы данных и использования схемы.

• Создайте login:

create login JohnDoe with password='saPass1'

create database my_db

create schema my_schema create user JohnDoe for login JohnDoe with default_schema = my_schema exec sp_addrolemember 'db_owner', 'JohnDoe'

После этого необходимо указать параметр подключения currentSchema в свойстве connectionParams задачи сборки updateDb (или в свойствах проекта в Studio). На самом деле, данный параметр никак не обрабатывается драйвером JDBC для SQL Server, но он указывает Studio и плагину Gradle какую схему использовать.

task updateDb(dependsOn: assembleDbScripts, type: CubaDbUpdate) < dbms = 'mssql' dbmsVersion = '2012' host = 'localhost' dbName = 'my_db' connectionParams = ';currentSchema=my_schema' dbUser = 'JohnDoe' dbPassword = 'saPass1' >

Имейте в виду, что пересоздавать БД SQL Server из Studio или выполнением createDb в командной строке нельзя, так как использование не-дефолтной схемы требует ассоциации с пользователем. Однако можно выполнять Update database в Studio или updateDb в командной строке, и все необходимые таблицы будут созданы в существующей базе данных и в указанной схеме.

A Walkthrough of SQL Schema

We define default SQL Schema for a database user in the create database user window:

If we do not define any default schema for a user, SQL Server assumes dbo as the default schema. We can verify the default schema for a user using the following system function:

SELECT SCHEMA_NAME ( ) ;

.

Retrieve all schema and their owners in a database

We can query sys.schemas system table to find out schema in a database and their owners:

SELECT s . name AS schema_name ,
s . schema_id ,
u . name AS schema_owner
FROM sys . schemas s
INNER JOIN sys . sysusers u ON u . uid = s . principal_id
ORDER BY s . name ;

In the following screenshot, we can see master database schema and their owners:

SQL Schema is a database entity, and you might get a different result of the query in a user database. For example, the AdventureWorks database shows the following schema information:

Note: SQL Server does not allow users to create objects in sys and INFORMATION_SCHEMA. It is used for storing internal system objects.

Let’s create a new table in the AdventureWorks database using the following query:

CREATE TABLE DemoSchema
( ID INT IDENTITY ( 1 , 1 ) ,
Name VARCHAR ( 20 )

We did not specify any schema in the CREATE TABLE statement. It automatically uses dbo schema for the table because the current user default schema is dbo:

Specify default SQL schema while creating a new login

Let’s create a new SQL login and database user with a default schema other than dbo.

Go to Security and create a new login:

Provide the following inputs on the general page:

1. Enter a SQL user name
2. Choose SQL Server authentication method and enter a password for the user

Navigate to the User Mapping page and put a check on the AdventureWorks database:

Click on eclipse for default schema and browse available schema in the database. Let’s select schema [Person] as a default schema for this user:

Click OK and return to the user-mapping page. We can see that the new login is having access to the AdventureWorks database and its default schema is [Person]:

Click OK, and it creates the SQL login, database user in the AdventureWorks database. Connect to SQL instance with this login. Execute the create table statement without specifying a SQL Schema. We can see it creates the table in the default schema for the user, i.e. [Person]:

Alternatively, we can run the script by specifying a schema name.

Script to create a table in [Person] schema:

CREATE TABLE [ person ] . [ DemoSchema ]
( ID INT IDENTITY ( 1 , 1 ) ,
Name VARCHAR ( 20 )

Script to create a table in the dbo schema:

CREATE TABLE [ dbo ] . [ DemoSchema ]
( ID INT IDENTITY ( 1 , 1 ) ,
Name VARCHAR ( 20 )

Create a new SQL Schema in SQL Server

We can create schemas as per our requirements. We can create a schema in a specific database. Let’s create a new schema in the AdventureWorks database.

Expand the AdventureWorks database and go to Security:

Right-click on Schemas and click to New Schema:

Specify a new schema name and schema owner. You can search the schema owner from existing logins:

Instead of creating the schema, click on generate scripts to give you an equivalent T-SQL statement:

USE [ AdventureWorks ]
CREATE SCHEMA [ Customer ] AUTHORIZATION [ Demouser ]

In this syntax, we can see that:

• [Customer] is the new schema name that we want to create in the AdventureWorks database
• We specify a schema owner using the AUTHORIZATION keyword in the CREATE SCHEMA command

Execute the command and refresh the schema list (right-click on schemas and refresh). You can see a new schema [Customer] on this list:

Note: Earlier, we created a demo user with a default schema [Person]. The demo user is the schema owner of the customer schema as well. If we connect to SQL instance using the demo user and create objects without specifying a schema name, it still takes default schema as [Person].

Change SQL schema of an existing object in SQL Server

We can transfer objects to different schemas, as well. For example, let’s migrate a table from [dbo] owner to sales schema in the AdventureWorks database following these steps:

1. Right-click on the specific table name and choose Design option:
2. It opens the table designer. We can change table properties such as column data types, default values, schema using this designer. Click on Properties as shown in the following image: It opens the table properties. It shows the table schema, server name, identity column, lock escalation, and filegroup information:
3. Click on SQL Schema, and it opens the available scheme in the database:
4. Select the required schema [Sales], and it gives the warning: Changing the schema of this object will result in all current permissions on this object being dropped
5. Click on Yes to proceed:
6. Close the table designer and save the changes after clicking on Yes:
7. Refresh the tables in the database, and we can see the schema of this table changes from dbo to sales:

By default, SQL Server searches for the object in the default schema and dbo schema. If the object belongs to other than the default and dbo schema, we require to specify schema name while accessing the object. You get an error message if the object does not exist in the default or dbo schema:

We can specify a schema name in the following format to access an object.

In this command, [sales] is a schema and [Demoschema] is a table:

Online Documentation for SQL Manager for SQL server

Схема (Schema) является коллекцией объектов базы данных, принадлежащих одному пользователю и образующих одно пространство имен. Пространством имен является набор объектов, в котором не может быть повторяющихся имен. Например, две таблицы могут иметь одинаковое имя, если только они находятся в разных схемах. Объект базы данных, например, таблица, принадлежит схеме, а схема принадлежит пользователю или роли базы данных. Если владелец схемы покидает организацию, перед удалением выбывшего пользователя схема передается во владение новому пользователю или роли.

В редакторе схем Вы можете изменять свойства схемы.

Открыть схему в редакторе можно:

• двойным щелчком мыши по нужной схеме в окне проводника баз данных,
• выбрав пункт Edit Schema в контекстном меню схемы.

• Использование панели инструментов
• Редактирование свойств схемы
• Просмотр зависимостей объекта
• Редактирование описания
• Просмотр DDL
• Назначение прав

Компания

Решения

• Обслуживание баз данных
• Управление базами данных
• Перенос баз данных
• Анализ баз данных
• Компоненты

Группы продуктов

• для SQL Server
• для PostgreSQL Server
• для MySQL Server
• для Oracle
• для Interbase/Firebird
• для DB2
• Бесплатные утилиты

Ресурсы

• Центр поддержки
• FAQs
• Документация
• Лицензионное соглашение

© 1999-2023
ООО ЕМС Софтваре Девелопмент
Все права защищены.

© 1999-2023 ЕМС Софтваре Девелопмент
Все права защищены.

Сайт SQLManager.net использует файлы cookie, что необходимо для его полноценной работы, включая такие функции как авторизация пользователя, покупка продуктов, сбор статистики посещений (исключая персональные данные) и другие.

Нажмите OK, чтобы продолжить просмотр сайта.