根据表名得到除主键外的所有字段及字段类型

Create FUNCTION [dbo].[fn_FieldsTypeExceptPK]
(
    @TableName Varchar(50)
)
RETURNS Varchar(MAX)
AS
BEGIN
    DECLARE @ReturnValue As Varchar(MAX)
    Set @ReturnValue=''

    Declare @PKName As Varchar(50)

    SELECT @PKName=syscolumns.name
    FROM syscolumns,sysobjects,sysindexes,sysindexkeys
    WHERE syscolumns.id = OBJECT_ID(@TableName)
    AND sysobjects.xtype = 'PK'
    AND sysobjects.parent_obj = syscolumns.id
    AND sysindexes.id = syscolumns.id
    AND sysobjects.name = sysindexes.name
    AND sysindexkeys.id = syscolumns.id
    AND sysindexkeys.indid = sysindexes.indid
    AND syscolumns.colid = sysindexkeys.colid
    
    Declare @Count as Int
    
    Select @Count=Count(1)
    From syscolumns
    Where syscolumns.id = OBJECT_ID(@TableName)
    AND Upper(syscolumns.name)<>@PKName


    Declare @i as Int
    Set @i=1

    While  (@i<=@Count)
    Begin
        Declare @name As Varchar(100)

        Select @name=Rtrim(Fields.Name)
        From(
        Select convert(char(30),'@' + syscolumns.name)
        + Upper(rtrim(systypes.name))
        + Case When rtrim(systypes.name) in ('binary','char','nchar','nvarchar','varbinary','varchar') Then '(' + rtrim(convert(char(4),syscolumns.length/2)) + ')'
        When rtrim(systypes.name) not in ('binary','char','nchar','nvarchar','varbinary','varchar') Then ' '
        End
        As Name,Row_Number() Over(Order By syscolumns.colid) As Rn
        From syscolumns
        Join systypes on syscolumns.xtype = systypes.xtype
        Where id = OBJECT_ID(@TableName) And systypes.name <> 'sysname' AND Upper(syscolumns.name)<>@PKName
        ) As Fields
        Where Fields.Rn=@i

        Set @ReturnValue=@ReturnValue+@name
        IF(@i<@Count)
        Begin
            Set @ReturnValue=@ReturnValue+','
        End
        Set @i=@i+1
    End

    RETURN @ReturnValue

END