sql server语言 - 基于表/列存在的存储过程逻辑

Question

我有一个庞大的存储过程集，从我们正在工作的各种环境中复制一个实体(跨越30多个表):Dev/Integration/PreProd/Prod。

这个想法是有时DB结构会从一个平台改变到另一个平台。一般来说，我们只做一些小的改变，比如将1-2列移动到一个新表中，或者在结构中添加一个新表，等等。

考虑到这种复制的复杂性，我希望在所有环境中都有这些过程的单一版本，而不管与DB结构相关的差异，以便可以从任何环境复制到另一个环境。例如，如果列存在于src和dest =>中，执行该逻辑，否则执行其他逻辑，等等。

这个想法是，即使代码检查列是否存在，我也不能在存储过程中使用不存在的列，并且该分支代码永远不会执行。

最好的选择是用sp_executesql编写代码，但代码变得非常复杂:考虑到太多，一旦在所有环境中对结构进行更改，我将不得不丢弃部分代码。

Answer 1

我会确保您需要的所有表和所有未来的表都有一个可识别的命名约定，以便您可以使用sys查询要复制的表/列。All_objects和sys。all_columns表。在本例中，您可以通过@SourceDB参数将源数据库名称传递给存储过程，并在源/目标表/列名相交的地方执行插入操作。没有指定目标数据库，因为它被假定为当前数据库:

-- get intersecting table/column names from @SourceDB
-- and Destination (Current) DB
declare @sql nvarchar(max)
set @sql = 'select o.name,c.name
    from ' + @SourceDB + '.sys.all_objects o
        inner join ' + @SourceDB + '.sys.all_columns c
            on c.object_id = o.object_id
    where o.type = ''U''
    and o.name like ''SomeStringToIdentifyYourTables%''
    intersect
    select o.name,c.name
    from sys.all_objects o
        inner join sys.all_columns c
            on c.object_id = o.object_id
    where o.type = ''U''
    and o.name like ''SomeStringToIdentifyYourTables%'''   
declare @TblColNames table(TblName nvarchar(200),ColName nvarchar(200))
insert into @TblColNames
exec(@sql)
-- insert data from Source DB to Destination (Current) DB
-- where Table/Column Names Intersect
declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + 'insert into ' +
    o.name + ' (' + replace((
                    select c.name +
                        case row_number() over(order by c.name desc)
                        when 1 then '' else '||' end
                    from @TblColNames c
                    where c.TblName = o.TblName
                    order by c.name asc
                    for xml path('')),'||',',') + ')
    select ' + replace((
                    select c.name +
                        case row_number() over(order by c.name desc)
                        when 1 then '' else '||' end
                    from @TblColNames c
                    where c.TblName = o.TblName
                    order by c.name asc
                    for xml path('')),'||',',') + '
    from ' + @SourceDB + '..' + o.name + '
'
from @TblColNames o
exec(@sql)