December 10, 2009 at 1:26 am
Comments posted to this topic are about the item Generate stored procedure parameter list based on table order by table
December 28, 2009 at 7:25 am
Customer table? Change what customer table?
December 28, 2009 at 8:12 am
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
May 23, 2016 at 7:14 am
Thanks for the script.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply