system table 查詢有關 identity 型態的欄位

-- 查詢identity型態的欄位

use DB
go
select a.name as table_name, b.name as identity_column_name
from sysobjects a, syscolumns b
where a.type = 'U' -- U指 table
and a.id = b.id
and b.status = 128  --128指 identity 欄位
go

-- 查詢 identity 型態有設 identity_gap 的欄位

use DB
go
select *
from sysindexes
where isnull(identitygap,0) <> 0
go