CREATE proc GetProc
(
@tableName nvarchar(100),
@procName nvarchar(100)
)
as
--declare some variables that used by cursor
declare @columnName nvarchar(100),
@typeName nvarchar(100),
@IsFixLength bit,
@IsDecimal bit,
@precision int,
@scale int ,
@max_length int
--declare some variables that composed the procedure
declare @sql1 nvarchar(1000),
@sql2 nvarchar(1000),
@sql3 nvarchar(1000)
set @sql1='create proc '+@procName+char(13)
set @sql1=@sql1+'('+char(13)
set @sql2='insert into '+@tableName+char(13)+'('
set @sql3='values'+char(13)
set @sql3='values'+char(13)+'('
DECLARE proc_cur CURSOR FOR
SELECT c.name ColumnName,t.name typeName,case
when t.name='bigint' THEN 1
when t.name='bit' THEN 1
when t.name='datetime' THEN 1
when t.name='float' THEN 1
when t.name='image' THEN 1
when t.name='int' THEN 1
when t.name='money' THEN 1
when t.name='real' THEN 1
when t.name='smalldatetime' THEN 1
when t.name='smallint' THEN 1
when t.name='smallmoney' THEN 1
when t.name='tinyint' THEN 1
when t.name='uniqueidentifier' THEN 1
else 0
end as IsFixLength,
Case
when t.name='decimal' THEN '1'
when t.name='numeric' THEN '1'
else 0
end as IsDecimal,
c.precision,c.scale,c.max_length
FROM sys.columns c inner join sys.types t on c.user_type_id=t.user_type_id where c.object_id=object_Id(@tableName)
and t.name<>'timestamp' and is_computed=0
OPEN proc_cur;
FETCH NEXT FROM proc_cur
into @columnName,@typeName,@IsFixLength,@IsDecimal,@precision,@scale,@max_length
WHILE @@FETCH_STATUS = 0
BEGIN
if @IsFixLength=1
begin
set @sql1=@sql1+'@'+@columnName+' '+@typeName+','+char(13)
end
else if @IsDecimal=1
set @sql1=@sql1+'@'+@columnName+' '+@typeName+'('+cast(@precision as varchar(4))+','+cast(@precision as varchar(4))+'),'+char(13)
else
set @sql1=@sql1+'@'+@columnName+' '+@typeName+'('+cast(@max_length as varchar(4))+'),'+char(13)
set @sql2=@sql2+'['+@columnname+'],'
set @sql3=@sql3+'@'+@columnName+','
FETCH NEXT FROM proc_cur into @columnName,@typeName,@IsFixLength,@IsDecimal,@precision,@scale,@max_length
END;
CLOSE proc_cur;
DEALLOCATE proc_cur;
--remove the last comma and char(13)
set @sql1=left(@sql1,len(@sql1)-2)
--add the )
set @sql1=@sql1+char(13)+')'+char(13)+'as'+char(13)
set @sql2=left(@sql2,len(@sql2)-1) +')'
set @sql3=left(@sql3,len(@sql3)-1) +')'
print @sql1
print @sql2
print @sql3 posted on 2008-07-10 11:26
stswordman 阅读(99)
评论(0) 编辑 收藏 所属分类:
sql