SQLServer存储过程中抽取表字段名组成字符串的自定义函数

存储过程中经常有语句需要引用某个表的所有字段名，写了个通用的表字段名抽取并组成字符串的函数

 

/****** Object:  UserDefinedFunction [dbo].[F_GetTableColumnName]    Script Date: ******/

 SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

 

-- =============================================  LOG:查出表的所有字段名称并组成字符串 -- Version：V1.0

--  SELECT dbo.F_GetTableColumnName(tablename) -- ============================================= 

CREATE  FUNCTION [dbo].[F_GetTableColumnName]     (       @Tablename NVARCHAR(200)     ) RETURNS VARCHAR(2000) AS     BEGIN

--申明游标        

DECLARE MyCursor CURSOR          FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID(@Tablename)

--打开游标         

OPEN MyCursor

--循环游标        

 DECLARE @columnnamelist NVARCHAR(2000) ,             @column_name NVARCHAR(100)        

FETCH NEXT FROM  MyCursor INTO @column_name        

SELECT  @columnnamelist = @column_name        

WHILE @@FETCH_STATUS = 0            

　　 BEGIN                 FETCH NEXT FROM  MyCursor INTO @column_name                

　　　　　　　IF @@FETCH_STATUS <> 0                     RETURN @columnnamelist

                SELECT  @columnnamelist = @columnnamelist + ',' + @column_name

            END 

--关闭游标        

 CLOSE MyCursor

--释放资源        

DEALLOCATE MyCursor

 

 

        RETURN @columnnamelist    

END

GO