Find current identity value for all tables in given database.

USE TEST

GO

IF OBJECT_ID('tempdb..#identityinfo','U') is not null

drop table tempdb..#identityinfo

GO

create table #identityinfo

(

id int identity(1,1) not null,

table_name varchar(256),

Column_name varchar(256),

Data_type varchar(256),

max_value bigint,

current_value bigint

)

GO

declare @table_name varchar(256),@column_name varchar(256),@data_type varchar(256),@Current_idenity bigint,@max_value bigint

declare C_identity cursor for select s.name+'.'+o.name,c.name,sc.DATA_TYPE from sys.columns c join sys.objects o on c.object_id = o.object_id

join sys.schemas s on s.schema_id = o.schema_id

join INFORMATION_SCHEMA.COLUMNS sc on sc.Table_schema=s.name and sc.TABLE_NAME=o.name and sc.COLUMN_NAME=c.name

where o.type = 'U' and c.is_identity = 1

order by o.name

OPEN C_identity

fetch next from C_identity into @table_name,@column_name,@data_type

while @@FETCH_STATUS=0

begin

SET @max_value = CASE @data_type

WHEN N'TinyInt' THEN 255

WHEN N'SmallInt' THEN 32767

WHEN N'Int' THEN 2147483647

WHEN N'BigInt' THEN 9223372036854775807

END;

select @Current_idenity=ident_current(@table_name)

insert into #identityinfo(table_name ,Column_name ,Data_type ,max_value ,current_value)

select @table_name,@column_name,@data_type,@max_value,@Current_idenity

fetch next from C_identity into @table_name,@column_name,@data_type

end

select * from #identityinfo

close C_identity

deallocate C_identity