SSIS: generate create table DDL programmatically

Is there source or library somewhere that would help me generate DDL on the fly?

I have a few hundred remote databases that I need to copy to the local server. Upgrade procedure in the field is to create a new database. Locally, I do the same.

So, instead of generating the DDL for all the different DB versions in the field, I’d like to read the DDL from the source tables and create an identical table locally.

Is there such a lib or source?

Answer

Actually, you will discover that your can do this yourself and you will learn something in the process. I use this on several databases I maintain. I create a view that makes it easy to look use DDL style info.

create view vw_help as
select 
  Table_Name as TableName
, Column_Name as ColName
, Ordinal_Position as ColNum
, Data_Type as DataType
, Character_Maximum_Length as MaxChars
, coalesce(Datetime_Precision, Numeric_Precision) as [Precision]
, Numeric_Scale as Scale
, Is_Nullable as Nullable
, case when (Data_Type in ('varchar', 'nvarchar', 'char', 'nchar', 'binary', 'varbinary')) then
       case when (Character_Maximum_Length = -1) then Data_Type + '(max)'
                  else Data_Type + '(' + convert(varchar(6),Character_Maximum_Length) + ')'
                  end
       when (Data_Type in ('decimal', 'numeric')) then
            Data_Type + '(' + convert(varchar(4), Numeric_Precision) + ',' + convert(varchar(4), Numeric_Scale) + ')'
       when (Data_Type in ('bit', 'money', 'smallmoney', 'int', 'smallint', 'tinyint', 'bigint', 'date', 'time', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset', 'datetime2', 'float', 'real', 'text', 'ntext', 'image', 'timestamp', 'uniqueidentifier', 'xml')) then Data_Type
  else 'unknown type'
  end as DeclA
, case when (Is_Nullable = 'YES') then 'null' else 'not null' end as DeclB
, Collation_Name as Coll
-- ,* 
from Information_Schema.Columns
GO

And I use the following to “show the table structure”

/*

exec ad_Help TableName, 1

*/

ALTER proc [dbo].[ad_Help] (@TableName nvarchar(128), @ByOrdinal int = 0) as
begin
set nocount on

declare @result table
(
  TableName nvarchar(128)
, ColName nvarchar(128)
, ColNum int
, DataType nvarchar(128)
, MaxChars int
, [Precision] int
, Scale int
, Nullable varchar(3)
, DeclA varchar(max)
, DeclB varchar(max)
, Coll varchar(128)
)

insert @result
select TableName, ColName, ColNum, DataType, MaxChars, [Precision], Scale, Nullable, DeclA, DeclB, Coll
from dbo.vw_help
where TableName like @TableName

if (select count(*) from @result) <= 0
begin
  select 'No tables matching ''' + @TableName + '''' as Error
  return
end

if (@ByOrdinal > 0)
begin
  select * from @result order by TableName, ColNum
end else begin
  select * from @result order by TableName, ColName
end

end
GO

You can use other info in InformationSchemas if you also need to generate Foreign keys, etc. It is a bit complex and I never bothered to flesh out everything necessary to generate the DDL, but you should get the right idea. Of course, I would not bother with rolling your own if you can use what has already been suggested.

Added comment — I did not give you an exact answer, but glad to help. You will need to generate lots of dynamic string manipulation to make this work — varchar(max) helps. I will point out the TSQL is not the language of choice for this kind of project. Personally, if I had to generate full table DDL’s I might be tempted to write this as a CLR proc and do the heavy string manipulation in C#. If this makes sense to you, I would still debug the process outside of SQL server (e.g. a form project for testing and dinking around). Just remember that CLR procs are Net 2.0 framework.

You can absolutely make a stored proc that returns a set of results, i.e., 1 for the table columns, 1 for the foreign keys, etc. then consume that set of results in C# and built the DDL statements. in C# code.