Schema - SQL SERVER

Um "schema" é uma estrutura lógica para objetos dentro de um banco de dados. Ele é usado principalmente para organizar e gerenciar objetos dentro de um banco de dados. Por exemplo, você pode ter um schema para as tabelas, outro para as views, e assim por diante, ou, dentro de um data mart você cria vários schemas, 1 por área de negócio e define o acesso ao usuário por schema. O schema fornece uma maneira de separar e organizar os objetos dentro de um banco de dados, facilitando a gestão e a segurança. É como se fosse uma "pasta" ou "espaço de nomes" para organizar os objetos de forma lógica.

Neste exemplo o meu banco de dados se chama DM_RH (Data Mart para a área de recursos humanos) e dentro deste data mart, criei o schema chamado RH.

Recomendo utilizar a camada medalhão, chamada de Bronze (Stage), Bronze (ODS) e DM (Ouro), na internet terão outros nomes mas o conceito é o mesmo.

• STAGE → dados brutos extraídos de fontes externas

• ODS (Operational Data Store) → dados tratados e integrados

• DM (Data Mart) → dados agregados e organizados para análise

Ou podemos criar os schemas de acordo com os nomes das áreas de negócio. Não existe certo ou errado, você deve criar aquilo que mais se adequa ao negócio.

USE seubanco;

GO

-- Criando o schema DM

CREATE SCHEMA DM;

GO

Outro exemplo de um banco de dados chamado DM e criando um schema por área com uma validação se o schema já existe.

USE DM;

GO

-- Cria o schema 'rh' se não existir

IF NOT EXISTS (SELECT 1 FROM sys.schemas WHERE name = 'rh')

BEGIN

    EXEC('CREATE SCHEMA rh');

END

GO

É importante o conceito de banco de dados e schema para que você não se confunda na hora de definir seu modelo de criação!

Banco de dados é um conjunto organizado de dados, onde ficam armazenadas todas as tabelas, views e objetos.

Schema é uma divisão lógica dentro do banco de dados, usada para organizar e agrupar objetos (tabelas, views, etc.) dentro do próprio banco.

Ou seja, o banco de dados é o “guarda-roupa”, e o schema são as “gavetas” dentro dele.

Para acessar os schemas criados, no Pesquisador de Objetos do lado esquerdo dentro do Management Studio, clique na setinha de + expandindo seu banco de dados, expanda Segurança, e Esquemas, e eles estarão lá.

Agora um passo a passo completo, criando um banco de dados e os schemas.

Criando um Banco de dados, arquivo .mdf e .log

Conectado ao seu servidor (pode ser local host. Clique aqui)

Logado com um usuário com permissões, em master, você deverá executar o script abaixo.

Caso deseje criar o banco em outro diretório, basta alterar o caminho C:\Data...

-- Criação do banco de dados datamart_BI

-- Obs: Precisa criar a pasta C:\Data

CREATE DATABASE datamart_BI

ON PRIMARY 

(

    NAME = 'datamart_BI_data',

    FILENAME = 'C:\Data\datamart_BI.mdf',  -- Ajuste o caminho conforme necessário

    SIZE = 100MB,                          -- Tamanho inicial

    MAXSIZE = UNLIMITED,                   -- Crescimento ilimitado

    FILEGROWTH = 50MB                      -- Incremento de crescimento

)

LOG ON 

(

    NAME = 'datamart_BI_log',

    FILENAME = 'C:\Data\datamart_BI.ldf',  -- Ajuste o caminho conforme necessário

    SIZE = 50MB,

    MAXSIZE = 2GB,

    FILEGROWTH = 25%

);

Criando schemas para algumas áreas

Após criar o banco de dados, clique na setinha azul para atualizar (ou F5) para o datamart aparecer, posteriormente, execute o script abaixo para criar os schemas e depois atualize de novo, e abra a pasta de segurança, esquemas, e lá estarão eles.

Criando uma tabela

USE seubanco; 

CREATE TABLE STAGE.nomeTabela (

    id INT IDENTITY(1,1) PRIMARY KEY,

    dataEntrada DATE,

    tipoTransacao VARCHAR(50),

    tipoOperacao CHAR(1),

    valor DECIMAL(18,2),

    titulo VARCHAR(100),

    descricao VARCHAR(255)

);

   Criando schema e transferindo uma tabela

CREATE SCHEMA NomeSchema;

GO

ALTER SCHEMA NomeSchema TRANSFER dbo.notas;

Copiando uma tabela de outro banco para este

financeiro é o nosso schema dentro do banco de dados data mart BI e iremos copiar a tabela selic que esta dentro de outro banco de dados.

USE datamart_BI;

GO

SELECT *

INTO financeiro.selic

FROM nomebancoorigem.dbo.selic;

Identificando quais schemas existem

SELECT name

FROM sys.schemas;

SELECT SCHEMA_NAME();

Identificando schemas de um banco específico:

USE stage;

GO

SELECT name

FROM sys.schemas

ORDER BY name;

Se quiser ver os schemas com tabelas associadas

USE stage;

GO

SELECT DISTINCT s.name AS SchemaName

FROM sys.schemas s

JOIN sys.tables t ON s.schema_id = t.schema_id

ORDER BY s.name;

Identificando quais tabelas existem em um schema

SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = 'financeiro' AND TABLE_NAME = 'selic';

Retornando nome das tabelas, nome database e nome do schema

SELECT 

    t.name AS 'Nome da Tabela',

    DB_NAME() AS 'Nome do Banco de Dados',

    s.name AS 'Nome do Schema'

FROM sys.tables t

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id;

Deletar um schema existente

DROP SCHEMA NomeDoSchema;

Alterando o nome de uma tabela dentro de um schema

Renomeia a tabela 'dbo.D_Ocorrencias' para 'DimOcorrencias' dentro do schema 'ito'

IF OBJECT_ID('dbo.D_Ocorrencias', 'U') IS NOT NULL

BEGIN

    -- Move para o schema 'ito'

    ALTER SCHEMA ito TRANSFER dbo.D_Ocorrencias;

    -- Renomeia a tabela

    EXEC sp_rename 'ito.D_Ocorrencias', 'DimOcorrencias';

END

GO