重庆思庄Oracle、Redhat认证学习论坛

标题: 通过SQL语句生成表字段变量申明 [打印本页]

作者: 郑全 时间: 2024-4-28 22:27
标题: 通过SQL语句生成表字段变量申明
本帖最后由郑全于 2024-4-28 22:28 编辑

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;