手头有一个:CREATE PROCEDURE getcolmemo1 @tbl_name varchar(1000) 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 INNER 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 open get_cursor fetch next from get_cursor into @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 GO 调用例子: getcolmemo1 表名
--or select column_name from information_schema.columns where table_name = '表名'
相关文章: 访问 Microsoft SQL Server 元数据的三种方法 http://www.microsoft.com/china/technet/columns/tipsfortechs/wsqs3.asp
select column_name from information_schema.columns where table_name = '表名'
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 INNER 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
open get_cursor
fetch next from get_cursor into @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
GO
调用例子:
getcolmemo1 表名
select column_name
from information_schema.columns
where table_name = '表名'
访问 Microsoft SQL Server 元数据的三种方法
http://www.microsoft.com/china/technet/columns/tipsfortechs/wsqs3.asp
from information_schema.columns
where table_name = '表名'