Файл формата SQL Server - можно ли использовать для создания оператора создания таблицы?

Question

Мне дали 35 файлов формата SQL Server (как xml, так и не xml) и связанные файлы данных. У меня нет DDL, необходимого для создания 35 таблиц.

Есть ли способ обратного инжиниринга файлов формата для создания оператора создания таблицы? Я знаю, что могу посмотреть на каждый файл и сделать это вручную, но пытаюсь понять, есть ли более простой способ...

Спасибо за любую помощь

Майк

Answer 1

Предположим, что ваш файл формата bcp xml выглядит примерно так:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="4"/>
  <FIELD ID="2" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
  <FIELD ID="3" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharPrefix" PREFIX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="150" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Id" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Type" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="3" NAME="Language" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="Value" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="Subject" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Этот сценарий sql генерирует оператор sql для создания таблицы из файла формата bcp xml.

SET NOCOUNT ON
DECLARE @filePath NVARCHAR(256) = N'C:\table.xml'
      , @tableName SYSNAME = N'#myTable'
      , @xmlData XML
      , @sqlCmd NVARCHAR(1000)
      , @dmlQuery NVARCHAR(max) = N''
      , @crlf NVARCHAR(2) = CHAR(13)+ CHAR(10)
DECLARE @columns table(id INT NOT NULL, colName SYSNAME NOT NULL, dataType VARCHAR(50), [Length] INT NULL, [Precision] INT NULL, [Scale] INT NULL)
DECLARE @sql table(s VARCHAR(1000), id INT IDENTITY)

SET @sqlCmd = N'SET @xmlData = (
  SELECT * FROM OPENROWSET (
    BULK ''' + @filePath  + ''', SINGLE_BLOB
  ) AS xmlData
)';
EXEC sp_executesql @sqlCmd, N'@xmlData XML OUTPUT', @xmlData = @xmlData OUTPUT

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/bulkload/format',
            'http://www.w3.org/2001/XMLSchema-instance' as xsi
)
INSERT INTO @columns
SELECT  x.c.value('@SOURCE', 'INT'),
        x.c.value('@NAME', 'varchar(100)'),
        SUBSTRING(x.c.value('@xsi:type', 'varchar(100)'), 4, 20),
        ISNULL(y.f.value('@MAX_LENGTH', 'INT'), y.f.value('@LENGTH', 'INT')),
        x.c.value('@PRECISION', 'INT'),
        x.c.value('@SCALE', 'INT')
FROM @xmldata.nodes('/BCPFORMAT/ROW/COLUMN') x(c)
JOIN @xmldata.nodes('/BCPFORMAT/RECORD/FIELD') y(f) ON x.c.value('@SOURCE', 'INT') = y.f.value('@ID', 'INT')

UPDATE @columns SET dataType = REPLACE(dataType, 'VARYCHAR', 'VARCHAR');
UPDATE @columns SET dataType = REPLACE(dataType, 'DATETIM4', 'SMALLDATETIME');

INSERT INTO  @sql(s) VALUES ('create table ' + QUOTENAME(@tableName) + ' (')

INSERT INTO  @sql(s)
    SELECT @crlf 
        + QUOTENAME([colName])+ N' ' + [dataType] 
        + IIF([Length] IS NOT NULL AND [dataType] LIKE 'N%CHAR', '(' + CAST([Length]/2 AS VARCHAR) + ')', 
          IIF([Length] IS NOT NULL AND [dataType] LIKE '%CHAR', '(' + CAST([Length] AS VARCHAR) + ')', 
          IIF([Length] IS NULL AND [dataType] LIKE '%CHAR', '(MAX)', 
          IIF([Precision] IS NOT NULL AND [dataType] = 'DECIMAL', '(' + CAST([Precision] AS VARCHAR) + IIF([Scale] IS NOT NULL, ',' + CAST([Scale] AS VARCHAR), '') + ' )'
         , '') ) ) ) + ',' 
    FROM @columns
    ORDER BY id;


UPDATE @sql 
   SET s = left(s, len(s) - 1) 
 WHERE id = scope_identity()

INSERT INTO @sql(s) VALUES( ')' )

SELECT @dmlQuery += s 
  FROM @sql 
 ORDER BY id;

PRINT @dmlQuery -- sql query CREATE TABLE ….

Затем вы можете заполнить только что созданную таблицу

BULK INSERT [#myTable] FROM 'C:\table.dat' WITH (KEEPIDENTITY, DATAFILETYPE='native', FORMATFILE = 'C:\table.xml');

Answer 2

Несмотря на то, что это сообщение пятилетней давности, оно хорошо сработало для меня в SQL 2019 (спасибо). Мне пришлось внести в него несколько изменений, чтобы удовлетворить мои потребности, поскольку были обнаружены определенные ограничения, которые вызывали проблемы.

Во-первых, оператор печати в конце ограничен 4000 байтами, и у меня было несколько таблиц, в которых код оператора создания превышал 7000, поэтому я изменил последнюю строку на PRINT CAST(@dmlQuery AS NTEXT), что, как я полагаю, позволяет вывести 16000 байт.

Во-вторых, в XML-файле было несколько собственных типов данных, которые не обрабатывались, поэтому я решил эту проблему, обновив временную таблицу @sql сразу после строки, вставляющей закрывающую скобку, следующим образом:update @sql set s = replace(s,'] UNIQUEID','] UNIQUEIDENTIFIER')update @sql set s = replace(s,'] FLT8','] FLOAT')

После добавления этих изменений я смог создавать таблицы из файлов формата bcp xml.

Опять же, я не знаю наверняка, связаны ли приведенные выше настройки с более новой версией SQL или нет, но мы оставляем их на случай, если кто-то еще столкнется с той же проблемой.