SQL Server 判斷資料表是否擁有 IDENTITY 自增欄位並查詢屬性

方法一：使用 OBJECTPROPERTY

DECLARE @Table_name varchar(60);
SET @Table_name = 'YourTable';

IF EXISTS (
    SELECT TOP 1 1 FROM sysobjects
    WHERE OBJECTPROPERTY(id, 'TableHasIdentity') = 1
      AND UPPER(name) = UPPER(@Table_name)
)
    SELECT 1;
ELSE
    SELECT 0;

方法二：結合 syscolumns 與 COLUMNPRPERTY

DECLARE @Table_name varchar(60);
SET @Table_name = 'YourTable';

IF EXISTS (
    SELECT TOP 1 1 FROM sysobjects so
    WHERE so.xtype = 'U'
      AND UPPER(so.name) = UPPER(@Table_name)
      AND EXISTS (
          SELECT TOP 1 1 FROM syscolumns sc
          WHERE sc.id = so.id
            AND COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1
      )
)
    SELECT 1;
ELSE
    SELECT 0;

進階：查詢自增欄位詳細屬性

DECLARE @Table_name varchar(60);
SET @Table_name = 'zy_cost_list';

SELECT so.name AS Table_name,          -- 資料表名稱
       sc.name AS Iden_Column_name,    -- 自增欄位名稱
       IDENT_CURRENT(so.name) AS curr_value,  -- 目前值
       IDENT_INCR(so.name) AS incr_value,     -- 遞增值
       IDENT_SEED(so.name) AS seed_value      -- 種子值
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
WHERE COLUMNPROPERTY(sc.id, sc.name, 'IsIdentity') = 1
  AND UPPER(so.name) = UPPER(@Table_name);

說明

• IDENT_CURRENT：回傳該資料表目前的身分識別值（跨 Session）
• IDENT_INCR / IDENT_SEED：定義時設定的遞增與種子值
• 適用於 SQL Server 2000 及以上版本