判断列名是否存在

实现动态化，为表添加存储时间字段，insus.net写一个存储过程，如下

set ansi_nulls on
go
set quoted_identifier on
go

-- =============================================
-- author:      insus.net
-- blog:        https://insus.cnblogs.com
-- create date: 2019-05-29
-- update date: 2019-05-29
-- description: 动态为表添加存储时间字段
-- =============================================
create procedure [dbo].[usp_dyanmicallyaddstoragedatetimefield]
(
    @table_catalog sysname,
    @table_schema sysname,
    @table_name sysname,    
    @columns nvarchar(max) -- '[column1],[column2],[column3]...'
)
as
begin
    declare @source table ([id] int identity(1,1), [value] nvarchar(max))
    insert into @source ([value]) select [value] from [dbo].[tvf_convertstringtotable](@columns,',')
    declare @r int = 1,@rs int = 0 
    select @rs = max([id]) from @source

    while @r <= @rs
    begin        
        declare @column_name sysname
        select @column_name = convert(varchar(30), [value], 23) from @source where [id] = @r    

        execute('if [dbo].[usp_isexistscolumn]('''+ @table_catalog +''','''+ @table_schema +''','''+ @table_name +''','''+ @column_name +''') = 0 alter table '+ @table_name +' add '+ @column_name +' datetime')

        set @r= @r + 1
    end
end

上面存储过程代码#54行中有一个定义函数：[dbo].[tvf_convertstringtotable]()

是分割字符串转为表。函数详细代码参考这篇《展开中断或忽略的序号》

还有一个自定义函数：[dbo].[usp_isexistscolumn]()这是判断表的列是否存在。

参考：《判断列名是否存在》

接下来，insus.net演示这个存储过程[dbo].[usp_dyanmicallyaddstoragedatetimefield]：

if object_id('tempdb..#temprpt') is not null drop table #temprpt   
create table #temprpt
(   
    [caseno] bigint,
    [username] nvarchar(20)
)        


execute [dbo].[usp_dyanmicallyaddstoragedatetimefield] 'tempdb','dbo','#temprpt','[column1],[column2],[column3]'    

select * from #temprpt

判断列名是否存在

相关推荐