sql - SSIS:以编程方式生成创建表 DDL

Question

是否有源代码或库可以帮助我即时生成 DDL？

我有几百个远程数据库需要复制到本地服务器。现场升级程序是创建一个新的数据库。在本地，我也这样做。

因此，我不想在现场为所有不同的数据库版本生成 DDL，而是想从源表中读取 DDL 并在本地创建一个相同的表。

有没有这样的库或者源码？

Answer 1

实际上，您会发现自己可以做到这一点，并且您会在这个过程中学到一些东西。我在我维护的几个数据库上使用它。我创建了一个 View ，使用 DDL 样式信息可以轻松查看。

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

然后我使用以下内容来“显示表结构”

/*

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

如果您还需要生成外键等，您可以使用 InformationSchemas 中的其他信息。这有点复杂，我从来没有费心去充实生成 DDL 所需的一切，但你应该有正确的想法。当然，如果您可以使用已经建议的内容，我不会费心自己动手。

添加评论——我没有给你一个确切的答案，但很乐意提供帮助。您将需要生成大量动态字符串操作才能完成这项工作——varchar(max) 有帮助。我要指出的是，TSQL 不是此类项目的首选语言。就个人而言，如果我必须生成完整的表 DDL，我可能会想将其编写为 CLR proc 并在 C# 中执行繁重的字符串操作。如果这对你有意义，我仍然会调试 SQL Server 之外的过程(例如，用于测试和四处闲逛的表单项目)。请记住，CLR 过程是 Net 2.0 框架。

您绝对可以创建一个返回一组结果的存储过程，即 1 个用于表列，1 个用于外键，等等。然后在 C# 中使用该组结果并构建 DDL 语句。在 C# 代码中。