我在查询时经常要用到向临时表中插入identity(int,1,1)这样的方法，可有时如果查询的表中已经有identity属性时会出错，请问如何知道数据库中存在IDENTITY属性的表以及确定该表中的IDENTITY属性列名？
---------------------------------------------------------------
SQL Server2000:
Select table_name as 表名,column_name as 列名
from information_schema.columns
where columnproperty(object_id(table_name),column_name,'IsIdentity')=1
---------------------------------------------------------------
---find table having identity property in database
use PUBS
select table_name, ident_seed(table_name) as ident_seed
from information_schema.tables
where ident_seed(table_name) is not null
go
---find identity column in a table
declare @tablename varchar(50)
declare @colname varchar(50)
declare @isident int
declare @cnt int
set @isident=0
set @cnt=1
set @tablename='jobs'
IF OBJECTPROPERTY(object_id('jobs'),'TableHasIdentity')=1
begin
print 'the table has identity'
set @colname=COL_NAME(OBJECT_ID(@tablename), @cnt)
while @colname<>''
BEGIN
print 'column:'+@colname+' ...'
IF COLUMNPROPERTY( OBJECT_ID(@tablename),@colname,'IsIdentity')=1
begin set @isident=1 BREAK end
ELSE
begin
set @cnt=@cnt+1
set @colname=COL_NAME(OBJECT_ID(@tablename), @cnt)
end
END
END
if @isident=1
print '>>column:'+@colname+' is identity'
else
print 'no identity column'
go