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

最新推荐文章于 2023-05-11 09:37:07 发布

转载最新推荐文章于 2023-05-11 09:37:07 发布 · 654 阅读

文章标签：

#object #function

sql 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一个SQL Server函数，该函数用于从指定表中返回除主键外的所有字段名称，通过遍历系统表来实现。适用于需要动态生成SQL语句的场景。

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