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

Create FUNCTION [dbo].[fn_FieldsExceptPK]
(
      @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(50)

            Select @name=Fields.Name
            From(
            Select syscolumns.name,syscolumns.colid,Row_Number() Over(Order By syscolumns.colid) As Rn
            From syscolumns
            Where syscolumns.id = OBJECT_ID(@TableName)
            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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值