none
Script para montar o create de uma tabela RRS feed

  • Pergunta

  • Olá pessoal,

    Estou concluindo um sistema de gerenciamento de banco de dados onde o usuário pode realizar várias tarefas além do SQL como, gerar uma visão numa grid e numa ferramenta de pivot da DevExpress, localizar as tabelas relacionas e exibir seus relacionamentos numa grid tanto por uma tabela como por um campo chave, criar relatórios e consultas e salvá-los em banco, enfim. Sei que já existem programas voltados para isso mas estou implementando nele outros recursos baseados em minhas necessidades em relação ao sistema ERP TOTVS RM durante todos esses anos em que trabalho com esta ferramenta. Bom, mas voltando ao banco de dados, no SQL Server Manager tem um recurso bastante bacana que é gerar o código completo de create de uma tabela trazendo chaves primárias e secundárias, constraints, índices, etc. Como faço para gerar isso no meu sistema? 

    Grato,

    Ilano.

    quinta-feira, 12 de setembro de 2019 10:59

Respostas

  • Segue exemplo de geração de comando de criação de tabela.

    Supondo a existência da seguinte tabela:

    -- código #1
    CREATE TABLE Funcionário (Nome varchar(20) not null, [Data nascimento] date not null, Sexo char(1) not null);
    
    ALTER TABLE Funcionário add constraint I1_Funcionário primary key (Nome);
    ALTER TABLE Funcionário add constraint Sexo_Funcionário check (Sexo in ('M', 'F'));


     

    Eis código SQL para gerar a tabela:

    -- código #2 v3
    -- Autor: José Diz/BH

    declare @ComandoSQL nvarchar(4000); set @ComandoSQL= 'CREATE TABLE [dbo].[Funcionário] ('; SELECT @ComandoSQL+= quotename (COLUMN_NAME) + ' ' + DATA_TYPE + case when DATA_TYPE in ('varchar', 'char', 'nvarchar', 'nchar') then '(' + cast (CHARACTER_MAXIMUM_LENGTH as varchar(4)) + ')' else '' end + ', ' from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo'
            and TABLE_NAME = 'Funcionário'
      order by ORDINAL_POSITION; set @ComandoSQL= left (@ComandoSQL, len (@ComandoSQL) -1) + ');';

    -- primary key
    IF exists (SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                   where TABLE_SCHEMA = 'dbo'
                         and TABLE_NAME = 'Funcionário'
                         and CONSTRAINT_TYPE = 'PRIMARY KEY')
      begin
       SELECT @ComandoSQL+= ' ALTER TABLE [dbo].[Funcionário] add constraint '
                            + quotename (KCU.CONSTRAINT_NAME)
                            + ' primary key ('
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU
        where KCU.TABLE_SCHEMA = 'dbo'
              and KCU.TABLE_NAME = 'Funcionário'
              and KCU.ORDINAL_POSITION = 1;
     
      SELECT @ComandoSQL+= quotename (KCU.COLUMN_NAME) + ', '
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU
        where KCU.TABLE_SCHEMA = 'dbo'
              and KCU.TABLE_NAME = 'Funcionário'
        order by KCU.ORDINAL_POSITION;
        
      set @ComandoSQL= left (@ComandoSQL, len (@ComandoSQL) -1)
                     + ');';    
      end;

    -- check
    IF exists (SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                   where TABLE_SCHEMA = 'dbo'
                         and TABLE_NAME = 'Funcionário'
                         and CONSTRAINT_TYPE = 'CHECK')
      begin
       SELECT @ComandoSQL+= ' ALTER TABLE [dbo].[Funcionário] add constraint '
                            + quotename (TC.CONSTRAINT_NAME)
                            + ' check '
                            + CC.CHECK_CLAUSE + ';'
         from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
              inner join INFORMATION_SCHEMA.CHECK_CONSTRAINTS as CC
                     on CC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
                        and CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
         where TC.TABLE_SCHEMA = 'dbo'
               and TC.TABLE_NAME = 'Funcionário'
               and TC.CONSTRAINT_TYPE = 'CHECK'
      end;
    PRINT @ComandoSQL;

    O tratamento de CHECK foi simplificado, considerando a existência de uma única restrição do tipo CHECK para a tabela.

     

    Não é um processo simples utilizar os metadados para (re)criar os objetos (tabelas, chaves etc). É necessário conhecer bem as tabelas que armazenam os metadados e como elas se relacionam.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta ilanocf quinta-feira, 12 de setembro de 2019 16:37
    • Editado José Diz quinta-feira, 12 de setembro de 2019 18:57
    quinta-feira, 12 de setembro de 2019 14:46

Todas as Respostas

  • Ilano, o que você está à procura são os metadados. Uma forma de obter essas informações é consultar o INFORMATION_SCHEMA, que é padrão ANSI; ou seja, funciona em vários gerenciadores de banco de dados (sgbd). Para o SQL Server, outra forma é consultar o catálogo de objetos do sistema.

    Sugiro que utilize o Information Schema, pela compatibilidade com outros sgbd. No caso do SQL Server você obtém detalhes no documento Visões do esquema de informações do sistema.

    ---

    Eis algumas das visões do catálogo de objetos do sistema:

    ---

    Para iniciar seus estudos: Visões do catálogo de objetos.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.
    • Editado José Diz quinta-feira, 12 de setembro de 2019 15:44
    quinta-feira, 12 de setembro de 2019 12:05
  • Segue exemplo de geração de comando de criação de tabela.

    Supondo a existência da seguinte tabela:

    -- código #1
    CREATE TABLE Funcionário (Nome varchar(20) not null, [Data nascimento] date not null, Sexo char(1) not null);
    
    ALTER TABLE Funcionário add constraint I1_Funcionário primary key (Nome);
    ALTER TABLE Funcionário add constraint Sexo_Funcionário check (Sexo in ('M', 'F'));


     

    Eis código SQL para gerar a tabela:

    -- código #2 v3
    -- Autor: José Diz/BH

    declare @ComandoSQL nvarchar(4000); set @ComandoSQL= 'CREATE TABLE [dbo].[Funcionário] ('; SELECT @ComandoSQL+= quotename (COLUMN_NAME) + ' ' + DATA_TYPE + case when DATA_TYPE in ('varchar', 'char', 'nvarchar', 'nchar') then '(' + cast (CHARACTER_MAXIMUM_LENGTH as varchar(4)) + ')' else '' end + ', ' from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo'
            and TABLE_NAME = 'Funcionário'
      order by ORDINAL_POSITION; set @ComandoSQL= left (@ComandoSQL, len (@ComandoSQL) -1) + ');';

    -- primary key
    IF exists (SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                   where TABLE_SCHEMA = 'dbo'
                         and TABLE_NAME = 'Funcionário'
                         and CONSTRAINT_TYPE = 'PRIMARY KEY')
      begin
       SELECT @ComandoSQL+= ' ALTER TABLE [dbo].[Funcionário] add constraint '
                            + quotename (KCU.CONSTRAINT_NAME)
                            + ' primary key ('
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU
        where KCU.TABLE_SCHEMA = 'dbo'
              and KCU.TABLE_NAME = 'Funcionário'
              and KCU.ORDINAL_POSITION = 1;
     
      SELECT @ComandoSQL+= quotename (KCU.COLUMN_NAME) + ', '
        from INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KCU
        where KCU.TABLE_SCHEMA = 'dbo'
              and KCU.TABLE_NAME = 'Funcionário'
        order by KCU.ORDINAL_POSITION;
        
      set @ComandoSQL= left (@ComandoSQL, len (@ComandoSQL) -1)
                     + ');';    
      end;

    -- check
    IF exists (SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                   where TABLE_SCHEMA = 'dbo'
                         and TABLE_NAME = 'Funcionário'
                         and CONSTRAINT_TYPE = 'CHECK')
      begin
       SELECT @ComandoSQL+= ' ALTER TABLE [dbo].[Funcionário] add constraint '
                            + quotename (TC.CONSTRAINT_NAME)
                            + ' check '
                            + CC.CHECK_CLAUSE + ';'
         from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
              inner join INFORMATION_SCHEMA.CHECK_CONSTRAINTS as CC
                     on CC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
                        and CC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
         where TC.TABLE_SCHEMA = 'dbo'
               and TC.TABLE_NAME = 'Funcionário'
               and TC.CONSTRAINT_TYPE = 'CHECK'
      end;
    PRINT @ComandoSQL;

    O tratamento de CHECK foi simplificado, considerando a existência de uma única restrição do tipo CHECK para a tabela.

     

    Não é um processo simples utilizar os metadados para (re)criar os objetos (tabelas, chaves etc). É necessário conhecer bem as tabelas que armazenam os metadados e como elas se relacionam.

     


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Marcado como Resposta ilanocf quinta-feira, 12 de setembro de 2019 16:37
    • Editado José Diz quinta-feira, 12 de setembro de 2019 18:57
    quinta-feira, 12 de setembro de 2019 14:46
  • Muito show José Diniz! Cara, funcionou perfeitamente.

    Mais uma vez, muitíssimo obrigado.

    quinta-feira, 12 de setembro de 2019 16:36
  • Muito show José Diniz! Cara, funcionou perfeitamente.

    Mais uma vez, muitíssimo obrigado.

    Ilano, atento que o código #2 foi somente uma demonstração no uso do INFORMATION_SCHEMA. Para um gerador completo é necessário acrescentar a análise de vários outros objetos.

    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quinta-feira, 12 de setembro de 2019 17:30