根据表名得到除主键外的所有字段及字段类型_根据表名获取字段类型-CSDN博客

本文将展示如何通过SQL查询，获取指定表中除主键外的所有字段信息，包括字段名及其数据类型，适用于数据库管理和数据分析场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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