我帮你改了改,你试试:-)use pubs go create PROCEDURE getcolmemo1 @tbl_name varchar(1000) --@tbl_name是输入参数,用户定义的表 AS declare @col_name varchar(100) , @beizhu_col varchar(100), @v_char varchar(8000), @v_char1 varchar(8000) declare get_cursor cursor for SELECT dbo.syscolumns.name AS col_name, CONVERT(varchar(1000), dbo.sysproperties.[value]) AS beizhu_col FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id -- outer JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype left outer JOIN dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND dbo.syscolumns.colid = dbo.sysproperties.smallid where dbo.sysobjects.name=@tbl_name --声明一个游标get_cursor,从系统表中返回用户表的字段信息。 open get_cursor fetch next from get_cursor into @col_name,@beizhu_col --将游标中的记录逐条付给变量@col_name,@beizhu_col set @v_char='' set @v_char1='' while (@@fetch_status =0) begin --select @col_name,@beizhu_col if @beizhu_col is null begin set @beizhu_col='' --set @v_char=@v_char+@col_name+@beizhu_col+',' end else set @beizhu_col=' as '+@beizhu_col set @v_char=@v_char+@col_name+@beizhu_col+',' set @v_char1=@v_char1+@col_name+',' --print @col_name+@beizhu_col fetch next from get_cursor into @col_name,@beizhu_col --循环游标 end print left(@v_char,len(@v_char)-1) --print left(@v_char1,len(@v_char1)-1) Close get_cursor deallocate get_cursor GOexec getcolmemo1 'sales'
把@tbl_name 表的字段都列出来 罢了
go
create PROCEDURE getcolmemo1
@tbl_name varchar(1000) --@tbl_name是输入参数,用户定义的表
AS
declare @col_name varchar(100) ,
@beizhu_col varchar(100),
@v_char varchar(8000),
@v_char1 varchar(8000)
declare get_cursor cursor for
SELECT dbo.syscolumns.name AS col_name,
CONVERT(varchar(1000), dbo.sysproperties.[value]) AS beizhu_col
FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
-- outer JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
left outer JOIN dbo.sysproperties ON dbo.syscolumns.id = dbo.sysproperties.id AND
dbo.syscolumns.colid = dbo.sysproperties.smallid
where dbo.sysobjects.name=@tbl_name
--声明一个游标get_cursor,从系统表中返回用户表的字段信息。
open get_cursor
fetch next from get_cursor into @col_name,@beizhu_col
--将游标中的记录逐条付给变量@col_name,@beizhu_col
set @v_char=''
set @v_char1=''
while (@@fetch_status =0)
begin
--select @col_name,@beizhu_col
if @beizhu_col is null begin
set @beizhu_col=''
--set @v_char=@v_char+@col_name+@beizhu_col+','
end
else
set @beizhu_col=' as '+@beizhu_col
set @v_char=@v_char+@col_name+@beizhu_col+','
set @v_char1=@v_char1+@col_name+','
--print @col_name+@beizhu_col
fetch next from get_cursor into @col_name,@beizhu_col
--循环游标
end
print left(@v_char,len(@v_char)-1)
--print left(@v_char1,len(@v_char1)-1)
Close get_cursor
deallocate get_cursor
GOexec getcolmemo1 'sales'