Guia de referência de configuração de objetos dentro do banco do zero (Conceitual)

Receita Prática — OBJETOS DENTRO DO DATABASE — DO ZERO

OBJETIVO
========
Este guia é AUTOSSUFICIENTE: Começa do zero dentro do escopo permitido ao tenant,
Cria todos os objetos auxiliares necessários para demonstrar a funcionalidade,
consulta o estado criado, modifica, adiciona/remove componentes quando aplicável
e faz a limpeza final.

Não é necessário executar outro guia antes deste.

IMPORTANTE
==========
- Execute conectado com o LOGIN REAL DO TENANT.
- Funciona sem sa/sysadmin para validar Self-Service.
- Substitua apenas os valores marcados entre <...>.
- Execute bloco por bloco; não use "Execute All" sem ler as observações.
- Os nomes LAB_* são descartáveis e foram escolhidos para não colidir com objetos
de produção.
- Este roteiro foi reconstruído a partir de referências suportadas no ambiente SkyNova Shared SQL.

OBJETIVO
========
Criar uma pequena aplicação de laboratório inteiramente dentro do seu database especificado, sem
depender de tabela pré-existente.

Serão criados:
- dbo.LAB_R47_Cliente
- dbo.LAB_R47_Audit
- PK, DEFAULT e CHECK
- IX_LAB_R47_Cliente_Nome
- dbo.LAB_R47_seq
- dbo.LAB_R47_IdList
- dbo.LAB_R47_fn_Dobro
- dbo.LAB_R47_usp_Cliente_Get
- dbo.LAB_R47_vw_Ativos
- dbo.LAB_R47_tr_Audit
- dbo.LAB_R47_Cli (synonym)

Depois o guia:
- consulta tudo;
- adiciona coluna;
- altera procedure/view/index;
- adiciona/remove constraint;
- remove coluna;
- exclui tudo.

1. CONTEXTO
===========

USE [<MEU_DATABASE>];
GO
SELECT
   DB_NAME()                  AS DatabaseName,
   USER_NAME()                AS DatabaseUser,
   IS_ROLEMEMBER(N'db_owner') AS IsDbOwner;
GO

O cenário completo de administração pressupõe o perfil que possui as permissões
correspondentes no database, tipicamente db_owner.

2. LIMPEZA DE LAB ANTERIOR
==========================

IF OBJECT_ID(N'dbo.LAB_R47_Cli',N'SN') IS NOT NULL DROP SYNONYM dbo.LAB_R47_Cli;
IF OBJECT_ID(N'dbo.LAB_R47_vw_Ativos',N'V') IS NOT NULL DROP VIEW dbo.LAB_R47_vw_Ativos;
IF OBJECT_ID(N'dbo.LAB_R47_usp_Cliente_Get',N'P') IS NOT NULL DROP PROCEDURE dbo.LAB_R47_usp_Cliente_Get;
IF OBJECT_ID(N'dbo.LAB_R47_fn_Dobro',N'FN') IS NOT NULL DROP FUNCTION dbo.LAB_R47_fn_Dobro;
IF OBJECT_ID(N'dbo.LAB_R47_tr_Audit',N'TR') IS NOT NULL DROP TRIGGER dbo.LAB_R47_tr_Audit;
IF OBJECT_ID(N'dbo.LAB_R47_Audit',N'U') IS NOT NULL DROP TABLE dbo.LAB_R47_Audit;
IF OBJECT_ID(N'dbo.LAB_R47_Cliente',N'U') IS NOT NULL DROP TABLE dbo.LAB_R47_Cliente;
IF OBJECT_ID(N'dbo.LAB_R47_seq',N'SO') IS NOT NULL DROP SEQUENCE dbo.LAB_R47_seq;
IF TYPE_ID(N'dbo.LAB_R47_IdList') IS NOT NULL DROP TYPE dbo.LAB_R47_IdList;
GO

3. CRIAR TABELAS
================

CREATE TABLE dbo.LAB_R47_Cliente
(
   id int IDENTITY(1,1) NOT NULL
       CONSTRAINT PK_LAB_R47_Cliente PRIMARY KEY,
   nome varchar(100) NOT NULL,
   ativo bit NOT NULL
       CONSTRAINT DF_LAB_R47_Cliente_Ativo DEFAULT(1),
   criado_em datetime2(0) NOT NULL
       CONSTRAINT DF_LAB_R47_Cliente_Criado DEFAULT(SYSDATETIME()),
   CONSTRAINT CK_LAB_R47_Cliente_Nome CHECK(LEN(nome) > 0)
);
GO
CREATE TABLE dbo.LAB_R47_Audit
(
   audit_id bigint IDENTITY(1,1) NOT NULL
       CONSTRAINT PK_LAB_R47_Audit PRIMARY KEY,
   cliente_id int NULL,
   operacao varchar(20) NOT NULL,
   data_hora datetime2(0) NOT NULL
       CONSTRAINT DF_LAB_R47_Audit_Data DEFAULT(SYSDATETIME())
);
GO

4. CRIAR ÍNDICE
===============

CREATE NONCLUSTERED INDEX IX_LAB_R47_Cliente_Nome
ON dbo.LAB_R47_Cliente(nome)
INCLUDE(ativo);
GO

5. CRIAR SEQUENCE
=================

CREATE SEQUENCE dbo.LAB_R47_seq
   AS bigint
   START WITH 1000
   INCREMENT BY 1;
GO

6. CRIAR TABLE TYPE
===================

CREATE TYPE dbo.LAB_R47_IdList AS TABLE
(
   id int NOT NULL PRIMARY KEY
);
GO

7. CRIAR FUNCTION
=================

CREATE OR ALTER FUNCTION dbo.LAB_R47_fn_Dobro(@Valor int)
RETURNS int
AS
BEGIN
   RETURN @Valor * 2;
END;
GO

8. CRIAR PROCEDURE
==================

CREATE OR ALTER PROCEDURE dbo.LAB_R47_usp_Cliente_Get
   @Id int = NULL
AS
BEGIN
   SET NOCOUNT ON;
   SELECT id,nome,ativo,criado_em
   FROM dbo.LAB_R47_Cliente
   WHERE @Id IS NULL OR id=@Id
   ORDER BY id;
END;
GO

9. CRIAR VIEW
=============

CREATE OR ALTER VIEW dbo.LAB_R47_vw_Ativos
AS
SELECT id,nome,criado_em
FROM dbo.LAB_R47_Cliente
WHERE ativo=1;
GO

10. CRIAR TRIGGER
=================

CREATE OR ALTER TRIGGER dbo.LAB_R47_tr_Audit
ON dbo.LAB_R47_Cliente
AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON;
   INSERT dbo.LAB_R47_Audit(cliente_id,operacao)
   SELECT id,N'INSERT'
   FROM inserted;
END;
GO

11. CRIAR SYNONYM
=================

CREATE SYNONYM dbo.LAB_R47_Cli
FOR dbo.LAB_R47_Cliente;
GO

12. INSERIR E CONSULTAR
=======================

INSERT dbo.LAB_R47_Cliente(nome)
VALUES(N'Cliente LAB 1'),(N'Cliente LAB 2');
GO
SELECT * FROM dbo.LAB_R47_Cliente;
SELECT * FROM dbo.LAB_R47_Audit;
SELECT * FROM dbo.LAB_R47_vw_Ativos;
SELECT * FROM dbo.LAB_R47_Cli;
SELECT dbo.LAB_R47_fn_Dobro(10) AS Dobro;
EXEC dbo.LAB_R47_usp_Cliente_Get;
SELECT NEXT VALUE FOR dbo.LAB_R47_seq AS ProximoSequence;
GO

Resultado esperado:
- 2 clientes;
- 2 registros de audit;
- função retorna 20;
- procedure/view/synonym consultáveis;
- sequence >= 1000.

13. INVENTÁRIO
==============

SELECT
   name,
   type,
   type_desc,
   create_date,
   modify_date
FROM sys.objects
WHERE name LIKE N'LAB_R47_%'
ORDER BY type_desc,name;
SELECT
   name,
   type_desc,
   is_disabled
FROM sys.indexes
WHERE object_id=OBJECT_ID(N'dbo.LAB_R47_Cliente');
SELECT
   name,
   is_table_type
FROM sys.types
WHERE name=N'LAB_R47_IdList';
GO

14. ADICIONAR COLUNA
====================

ALTER TABLE dbo.LAB_R47_Cliente
ADD email varchar(255) NULL;
GO

15. MODIFICAR PROCEDURE
=======================

CREATE OR ALTER PROCEDURE dbo.LAB_R47_usp_Cliente_Get
   @Id int = NULL
AS
BEGIN
   SET NOCOUNT ON;
   SELECT id,nome,email,ativo,criado_em
   FROM dbo.LAB_R47_Cliente
   WHERE @Id IS NULL OR id=@Id
   ORDER BY id;
END;
GO

16. MODIFICAR VIEW
==================

CREATE OR ALTER VIEW dbo.LAB_R47_vw_Ativos
AS
SELECT id,nome,email,criado_em
FROM dbo.LAB_R47_Cliente
WHERE ativo=1;
GO

17. MODIFICAR ÍNDICE
====================

CREATE NONCLUSTERED INDEX IX_LAB_R47_Cliente_Nome
ON dbo.LAB_R47_Cliente(nome)
INCLUDE(ativo,email)
WITH (DROP_EXISTING=ON);
GO

18. ADICIONAR CONSTRAINT
========================

ALTER TABLE dbo.LAB_R47_Cliente
ADD CONSTRAINT CK_LAB_R47_Cliente_Email
CHECK(email IS NULL OR email LIKE N'%@%');
GO

19. REMOVER CONSTRAINT
======================

ALTER TABLE dbo.LAB_R47_Cliente
DROP CONSTRAINT CK_LAB_R47_Cliente_Email;
GO

20. REMOVER COLUNA COM DEPENDÊNCIAS CORRETAMENTE
================================================

Primeiro retire a coluna do índice:

CREATE NONCLUSTERED INDEX IX_LAB_R47_Cliente_Nome
ON dbo.LAB_R47_Cliente(nome)
INCLUDE(ativo)
WITH (DROP_EXISTING=ON);
GO

Depois retire a referência dos módulos:

CREATE OR ALTER PROCEDURE dbo.LAB_R47_usp_Cliente_Get
   @Id int = NULL
AS
BEGIN
   SET NOCOUNT ON;
   SELECT id,nome,ativo,criado_em
   FROM dbo.LAB_R47_Cliente
   WHERE @Id IS NULL OR id=@Id
   ORDER BY id;
END;
GO
CREATE OR ALTER VIEW dbo.LAB_R47_vw_Ativos
AS
SELECT id,nome,criado_em
FROM dbo.LAB_R47_Cliente
WHERE ativo=1;
GO

Agora remova:

ALTER TABLE dbo.LAB_R47_Cliente
DROP COLUMN email;
GO

21. EXCLUIR TUDO — ORDEM DE DEPENDÊNCIA
=======================================

DROP SYNONYM dbo.LAB_R47_Cli;
DROP VIEW dbo.LAB_R47_vw_Ativos;
DROP PROCEDURE dbo.LAB_R47_usp_Cliente_Get;
DROP FUNCTION dbo.LAB_R47_fn_Dobro;
DROP TRIGGER dbo.LAB_R47_tr_Audit;
DROP TABLE dbo.LAB_R47_Audit;
DROP TABLE dbo.LAB_R47_Cliente;
DROP SEQUENCE dbo.LAB_R47_seq;
DROP TYPE dbo.LAB_R47_IdList;
GO

22. VALIDAÇÃO FINAL
===================

SELECT name,type_desc
FROM sys.objects
WHERE name LIKE N'LAB_R47_%';
SELECT name,is_table_type
FROM sys.types
WHERE name=N'LAB_R47_IdList';
GO

Resultado esperado: zero linhas.

CHECKLIST
=========
[ ] Table
[ ] PK/Default/Check
[ ] Index
[ ] Sequence
[ ] Type
[ ] Function
[ ] Procedure
[ ] View
[ ] Trigger
[ ] Synonym
[ ] INSERT/SELECT
[ ] Add column
[ ] Alter procedure/view/index
[ ] Add/remove constraint
[ ] Remove column
[ ] Drop completo

Ajuda Skynova

Menu