/*
Creater:Kern
Date:2007-08-03
Function:构建查询语句列字段
Modify:1.增加排序处理,使输出结果与表单列结构一致;2.增加换行处理每8个字段换行一次(2007-08-08 by kern)
Remark:辅助开发存储过程
*/
CREATE procedure dbo.CCS
@Prefix varchar(10)='', --前缀
@TableName varchar(100), --表名
@Select int=0 --是否生成Select语句,参数1为生成,0为不生成
AS
declare
@ColumName varchar(50),
@ExecStr varchar(2000),
@CreateStr varchar(2000),
@NewLine int
Set @CreateStr=''
--Set @ExecStr='select name into ##Tmp from syscolumns where OBJECTPROPERTY(id, N''IsUserTable'') = 1 and id = object_id(N''[dbo].[' + @TableName + ']'')'
Set @ExecStr='select name into ##Tmp from syscolumns where id = object_id(N''[dbo].[' + @TableName + ']'') order by colid'
execute(@ExecStr)
set @NewLine=1
declare ColumsStrs cursor for
select name from ##Tmp
open ColumsStrs
fetch next from ColumsStrs into @ColumName
while @@fetch_status=0
begin
if @Prefix<>'' set @ColumName=@Prefix+'.'+@ColumName
Set @CreateStr=@CreateStr + @ColumName + ','
if @NewLine=8
begin
set @CreateStr=@CreateStr + convert(varchar,0x0A)
set @NewLine=0
end
set @NewLine = @NewLine + 1
fetch next from ColumsStrs into @ColumName
end
close ColumsStrs
deallocate ColumsStrs
if @Select=1
begin
if @Prefix<>''
begin
Set @CreateStr='select ' + substring(@CreateStr, 1, len(@CreateStr)-1) + convert(varchar,0x0A) + 'from ' + @TableName + ' ' + @Prefix
end
else
begin
Set @CreateStr='select ' + substring(@CreateStr, 1, len(@CreateStr)-1) + convert(varchar,0x0A) + 'from ' + @TableName
end
end
print @CreateStr
drop table ##Tmp
GO