With a few changes, it runs on SQL2000: (table names go into the WHERE clause on line 35, eg 'tIndividuals') - Merci, Jean-Pierre - RM
--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