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

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

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

文章标签：

#object #function

sql 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一个SQL Server自定义函数，该函数能够接收表名作为参数，并返回除了主键以外的所有字段名称及其对应的变量声明，适用于更新操作场景。通过解析系统目录视图来确定主键及其它字段。

Create FUNCTION [dbo].[fn_FieldsExceptPKAboutUpdate]
(
   @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+'=@'+@name
       IF(@i<@Count)
       Begin
           Set @ReturnValue=@ReturnValue+','
       End
       Set @i=@i+1
   End
   RETURN @ReturnValue

END