robjlong
4/6/2016 - 10:12 PM

Minimal reproduction of GetDropAndCreateDdl

Minimal reproduction of GetDropAndCreateDdl

CREATE TABLE dbo.so_36461498
(
    int_sk int NOT NULL
,   varchar_30 varchar(30) NOT NULL
,   nvarchar_30 nvarchar(30) NOT NULL
);

SELECT
    C.TABLE_SCHEMA
,   C.TABLE_NAME
,   C.COLUMN_NAME
,   C.DATA_TYPE
,   C.CHARACTER_MAXIMUM_LENGTH
,   C.CHARACTER_OCTET_LENGTH
FROM
    INFORMATION_SCHEMA.COLUMNS AS C
WHERE
    C.TABLE_NAME LIKE 'so_364%';

SELECT
    S.name
,   T.name
,   C.name
,   C.max_length
,   ST.name
FROM
    sys.schemas AS S
    INNER JOIN sys.tables AS T
    ON T.schema_id = S.schema_id
    INNER JOIN sys.columns AS C
    ON C.object_id = T.object_id
    INNER JOIN sys.types AS ST
    ON ST.user_type_id = C.user_type_id
WHERE
    T.name LIKE 'so_364%';
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement"  #>    
<#
var sourceConnection =  SchemaManager.CreateConnectionNode("tempdb", @"Provider=SQLNCLI11;Server=localhost\dev2014;Initial Catalog=tempdb;Integrated Security=SSPI;");
var importResult = sourceConnection.ImportDB("dbo", "so%",  ImportOptions.ExcludeColumnDefault | ImportOptions.ExcludeViews);

   foreach (var table in importResult.TableNodes) 
   {
        string fileName = string.Format(@"C:\ssisdata\so\{0}_{1}.sql", table.Name, table.Schema.Name);
       
        System.IO.File.WriteAllText(fileName, table.GetDropAndCreateDdl());
        
        fileName = string.Format(@"C:\ssisdata\so\{0}_{1}.getBiml", table.Name, table.Schema.Name);
        System.IO.File.WriteAllText(fileName, table.Columns.GetBiml());
   }
#>
</Biml>