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

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