通过SQL语句生成表字段变量申明

郑全

select
     case  data_type  
        when 'varchar' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' varchar(' + cast(a.CHARACTER_MAXIMUM_LENGTH as varchar) + ')'
        when 'int' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' int'
        when 'date' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' date'
        when 'datetime' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' datetime'
        when 'numeric' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' numeric(' + cast(a.NUMERIC_PRECISION  as varchar) + ',' + cast(a.NUMERIC_SCALE as varchar) + ')'
        when 'decimal' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' decimal(' + cast(a.NUMERIC_PRECISION as varchar) + ',' +  cast (a.NUMERIC_SCALE as varchar) + ')'
        when 'money' then  'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' money '
        when 'float' then   'declare @' + cast(column_name  as varchar) + space(20-len(column_name)) + ' float '
        else  'declare @' + cast(column_name  as varchar) + ' ' + space(20-len(column_name)) + cast(data_type as varchar)
        end
   from INFORMATION_SCHEMA.COLUMNS  a
   where table_catalog='bak'
    and table_name='V_BA_FIRSTPAGE'
    order by ORDINAL_POSITION;

登录/注册后可看大图