generate storedprocedure based on table

• jplebrasseur

  SSC Journeyman

  Points: 93

  More actions

  December 4, 2009 at 7:07 am

  #228389

  select '@' + c.name, col_definition =

  case t.name

  when 'bigint' then 'bigint,'

  when 'int' then 'int,'

  when 'smallint' then 'smallint,'

  when 'tinyint' then 'tinyint,'

  when 'bit' then 'bit,'

  when 'decimal' then 'decimal,'

  when 'numeric' then 'numeric,'

  when 'money' then 'money,'

  when 'smallmoney' then 'smallmoney,'

  when 'float' then 'float,'

  when 'real' then 'real,'

  when 'datetime' then 'datetime,'

  when 'smalldatetime' then 'smalldatetime,'

  when 'sql_variant' then 'sql_variant,'

  when 'timestamp' then 'timestamp,'

  when 'uniqueidentifier' then 'uniqueidentifier,'

  when 'xml' then 'xml,'

  else t.name + '(' + cast(c.prec as varchar(50)) + '),'

  end

  from sysobjects s

  inner join sys.syscolumns c on s.id = c.id

  inner join sys.types t on t.user_type_id = c.xtype

  where s.xtype='U'

  and (s.name = 'ProductProv' or s.name = 'ProductStatus')

  order by s.name , c.colid

  go