--Jean-Pierre Lebrasseur
--date 04-12-2009
--this script give you columns parameters order by table name and ordinal position by table.

declare @sqlstmt char(500)
declare @name char(50)
declare @coldefinition char(50)

declare CreateParam cursor for
 select 'declare @' + 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 syscolumns c on s.id = c.id
 inner join systypes t on t.xusertype = c.xtype
 where s.xtype='U' and 
 (s.name = 'tIndividuals' or s.name = 'ProductStatus' or s.name = 'ProductBrandCode')
 order by s.name , c.colid

open CreateParam

fetch next from CreateParam into @name, @coldefinition 

while @@fetch_status = 0
 begin
 set @sqlstmt = ''
 set @sqlstmt = rtrim(@sqlstmt) + rtrim(@name) + ' ' + rtrim(@coldefinition)
 
 print rtrim(@sqlstmt)
 fetch next from CreateParam into @name, @coldefinition 
 end

close CreateParam
deallocate CreateParam



go