GetTableFields --参数：传入要剔除的自增字段； '-1'-自动剔除自增字段； ''输出全部字段

来源：互联网发布：mac不能缓冲优酷视频编辑：程序博客网时间：2024/05/29 00:30

CREATE FUNCTION [dbo].[fn_GetTableFields]
(
@tbName varchar(50), --表名
@iField varchar(50)  --参数：传入要剔除的自增字段； '-1'-自动剔除自增字段； ''输出全部字段
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @IDEfield sysname
IF @iField='-1'
  SELECT @IDEfield=c.name FROM sysobjects o, syscolumns c, systypes t
   WHERE o.name=@tbName AND o.id=c.id AND c.xusertype=t.xusertype AND c.status=0x80
ELSE
  SELECT @IDEfield=@iField

DECLARE @fName varchar(50),@S varchar(1000)
  SET @S=''
  DECLARE curF CURSOR FOR
  SELECT a.name FROM syscolumns a, systypes b
  WHERE a.xtype=b.xusertype and a.id=(SELECT id FROM sysobjects WHERE xtype='U' and NAME=@tbName)
OPEN curF
FETCH NEXT FROM curF INTO @fName

WHILE @@FETCH_STATUS=0
BEGIN
  IF Upper(@IDEfield)<>Upper(@fName)
   SET @S=@S+@fName+', '

  FETCH NEXT FROM curF INTO @fName
END
CLOSE curF
DEALLOCATE curF

SET @S=LEFT(@S,LEN(RTRIM(@S))-1)
--SET @S=' SELECT ' + RTRIM(@S) +' FROM ' + @tbName + ' '
RETURN(@S)
END