CREATE FUNCTION [dbo].fGetTabCol, @prefix Varchar(20) = ‘’)
    Returns Varchar(8000)
    As
    Begin
    Declare
    @cols NVarchar(2000),
    @cname Varchar(100),
    @id Int,
    @maxid Int
    —— 表变量要单独声明
    Declare @t_col Table(ID Integer Identity(1, 1) Not Null, CNAME Varchar(100) Not Null)
    Insert Into @t_col(CNAME)
    Select NAME
    From SYSCOLUMNS
    Where ID = Object_ID(@tname)
    Order By COLID ASC
    Select @cols = ‘’, @id = 1, @maxid = Max(ID) From @t_col
    WHILE(@id <= @maxid)
    Begin
    Select @cname = CNAME From @t_col Where ID =@id

    If @prefix Is Null Or LTRIM(RTRIM(@prefix)) = ‘’
    Begin
    If @id = @maxid
    Begin
    Select @cols = @cols + @cname
    End
    Else
    Begin
    Select @cols = @cols + @cname + ‘, ‘
    End
    End
    Else
    Begin
    If @id = @maxid
    Begin
    Select @cols = @cols + @prefix + ‘.’ + @cname
    End
    Else
    Begin
    Select @cols = @cols + @prefix + ‘.’ + @cname + ‘, ‘
    End
    End
    Select @id = @id + 1
    End
    RETURN(@cols)
    End
    GO