Início / dba / Perguntas / 53085
Accepted
Andrew Savinykh
Asked: 2013-11-11 15:59:19 +0800 CST

Existe uma maneira de gerar o script de criação de tabela em TSQL?

  • 772

Existe uma maneira de gerar um script de criação a partir de uma tabela existente puramente em T-SQL (ou seja, sem usar o SMO, pois o T-SQL não tem acesso ao SMO). Digamos que um procedimento armazenado que recebe um nome de tabela e retorna uma string que contém o script de criação para a tabela fornecida?

Agora deixe-me descrever a situação que estou enfrentando, pois pode haver uma maneira diferente de abordar isso. Eu tenho uma instância com várias dezenas de bancos de dados. Todos esses bancos de dados têm o mesmo esquema, todas as mesmas tabelas, índice e assim por diante. Eles foram criados como parte de uma instalação de software de terceiros. Eu preciso ter uma maneira de trabalhar com eles para que eu possa agregar dados deles de maneira ad-hoc. Boas pessoas do dba.se já me ajudaram aqui Como criar uma trigger em um banco de dados diferente?

Atualmente preciso encontrar uma maneira de fazer uma seleção de uma tabela em todos os bancos de dados. Gravei todos os nomes de banco de dados em uma tabela chamada Databaseese escrevi o seguinte script para executar uma instrução select em todos eles:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) = 
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

No entanto, o script acima falha com a seguinte mensagem:

Um valor explícito para a coluna de identidade na tabela '#tmp' só pode ser especificado quando uma lista de colunas for usada e IDENTITY_INSERT estiver ON.

Adicionando isso:

SET IDENTITY_INSERT #tmp ON

não ajuda, pois não consigo especificar a lista de colunas e mantê-la genérica.

No SQL, não há como desativar a identidade de uma determinada tabela. Você só pode soltar uma coluna e adicionar uma coluna, o que obviamente altera a ordem das colunas. E se a ordem das colunas mudar, você, novamente, precisa especificar a lista de colunas, que seria diferente dependendo da tabela consultada.

Então, eu estava pensando se eu pudesse obter o script de criação de tabela no meu código T-SQL, eu poderia manipulá-lo com expressões de manipulação de string para remover a coluna de identidade e também adicionar uma coluna para o nome do banco de dados ao conjunto de resultados.

Alguém pode pensar em uma maneira relativamente fácil de alcançar o que eu quero?

sql-server sql-server-2012

4 respostas

  1. Best Answer

    Em 2007, pedi uma maneira fácil de gerar um CREATE TABLEscript via T-SQL em vez de usar a interface do usuário ou SMO. Fui sumariamente rejeitado .

    No entanto, o SQL Server 2012 torna isso muito fácil. Vamos fingir que temos uma tabela com o mesmo esquema em vários bancos de dados, por exemplo dbo.whatcha:

    CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1), 
  x VARCHAR(MAX), 
  b DECIMAL(10,2), 
  y SYSNAME
);

    O script a seguir usa a nova sys.dm_exec_describe_first_results_setfunção de gerenciamento dinâmico para recuperar os tipos de dados apropriados para cada uma das colunas (e ignorando a IDENTITYpropriedade). Ele cria a tabela #tmp que você precisa, insere de cada um dos bancos de dados em sua lista e seleciona de #tmp, tudo dentro de um único lote SQL dinâmico e sem usar um WHILEloop (isso não o torna melhor, apenas mais simples de olhar e permite que você ignore Database_Ref_Nocompletamente :-)).

    SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

    PRINTA saída resultante :

    CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

    Quando você estiver confiante de que está fazendo o que você espera, apenas remova o comentário do arquivo EXEC.

    (Isso confia em você que o esquema é o mesmo; ele não valida que uma ou mais das tabelas foi alterada e pode falhar como resultado.)

    • 28

  2. Não é possível no T-SQL gerar um script de criação completo de uma tabela. Pelo menos não há nenhuma construção no caminho. você sempre pode escrever seu próprio "gerador" passando pelas informações sys.columns.

    Mas no seu caso você não precisa obter o script de criação completo. Tudo o que você precisa é impedir que a SELECT INTOpropriedade de identidade seja copiada. A maneira mais fácil de fazer isso é adicionar um cálculo a essa coluna. Então, em vez de

    select * into #tmp from Database1.dbo.Table1 where 1=0

    você precisa escrever

    select id*0 as id, other, column, names into #tmp from Database1.dbo.Table1 where 1=0

    Para gerar esta instrução, você pode usar novamente sys.columns como neste SQL Fiddle

    Configuração do esquema do MS SQL Server 2008 :

    CREATE TABLE dbo.testtbl(
    id INT IDENTITY(1,1),
    other NVARCHAR(MAX),
    [column] INT,
    [name] INT
);

    As duas colunas que precisamos são namee is_identity: Consulta 1 :

    SELECT name,is_identity
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

    Resultados :

    |   NAME | IS_IDENTITY |
|--------|-------------|
|     id |           1 |
|  other |           0 |
| column |           0 |
|   name |           0 |

    Com isso podemos usar uma CASEinstrução para gerar cada coluna para a lista de colunas:

    Pergunta 2 :

    SELECT ','+ 
    CASE is_identity
    WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
    ELSE QUOTENAME(name)
    END
  FROM sys.columns
 WHERE object_id = OBJECT_ID('dbo.testtbl');

    Resultados :

    |        COLUMN_0 |
|-----------------|
| ,[id]*0 AS [id] |
|        ,[other] |
|       ,[column] |
|         ,[name] |

    Com um pequeno truque de XML, podemos concatenar tudo isso para obter a lista completa de colunas:

    Pergunta 3 :

    SELECT STUFF((
  SELECT ','+ 
      CASE is_identity
      WHEN 1 THEN QUOTENAME(name)+'*0 AS '+QUOTENAME(name)
      ELSE QUOTENAME(name)
      END
    FROM sys.columns
   WHERE object_id = OBJECT_ID('dbo.testtbl')
   ORDER BY column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'')

    Resultados :

    |                               COLUMN_0 |
|----------------------------------------|
| [id]*0 AS [id],[other],[column],[name] |

    Lembre-se de que você não pode criar uma tabela #temp usando SQL dinâmico e usá-la fora dessa instrução, pois a tabela #temp sai do escopo quando sua instrução sql dinâmica termina. Portanto, você precisa espremer todo o seu código na mesma string SQL dinâmica ou usar uma tabela real. Se você precisa executar vários desses scripts/procedimentos ao mesmo tempo, você precisa de um nome de tabela aleatório, caso contrário, eles irão pisar um no outro. Algo como QUOTENAME(N'temp_'+CAST(NEWID() AS NVARCHAR(40))deve fazer um nome bom o suficiente.

    Em vez de copiar todos os dados, você também pode usar uma técnica semelhante para gerar automaticamente uma exibição para cada tabela que une todas as encarnações dessa tabela em todos os bancos de dados. Dependendo do tamanho da tabela, no entanto, isso pode ser mais rápido ou mais lento, então você deve testá-lo. Se você seguir esse caminho, eu colocaria essas visualizações em um banco de dados separado.

    • 5

  3. Existe um bom script para conseguir isso no artigo SQLServerCentral:

    A versão mais recente do script também está disponível como texto aqui (stormrage.com).

    Eu gostaria que houvesse uma maneira de incluir todo o script aqui, porque funciona para mim. O script é muito longo para colar aqui.

    Aviso de direitos autorais:

    --#################################################################################################
-- copyright 2004-2013 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
-- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt
--Purpose: Script Any Table, Temp Table or Object
--
-- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced.The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#################################################################################################
    • 4

  4. Você pode gerar um rascunho CREATE TABLEusando SQL dinâmico a partir dos dados em INFORMATION_SCHEMA.COLUMNS.

    Se você precisar adicionar restrições, etc., precisará adicionar informações de algumas das outras INFORMATION_SCHEMAvisualizações.

    Documentação da Microsoft

    • -1

relate perguntas