Hi All,

How I can get the identity seed value and increment value without using ident_seed() and ident_incr() function?

My stored procedure will generate script of tables from another database and my stored procedure is other database and I need to know the increment and seed value of identity.

example
In Nortwind database, I have the following stored procedure:

use northwind
go
create procedure usp_GenerateScriptTest
@TargetDB sysname,
@TableName sysname
as
begin
declare @SqlCmd varchar(5000)

select @SqlCmd = '
select o.name as TableName,
c.name as ColName,
(c.colstat & 1) as IsIdentityField,
IDENT_SEED (''' + @TableName +''') as Seed,
IDENT_INCR (''' + @TableName +''') as Increment
from ' + @TargetDB + '.dbo.sysobjects o
left outer join ' + @TargetDB + '.dbo.syscolumns c on c.id = o.id
where o.name = ''' + @TableName + '''
order by c.colorder'

exec(@SqlCmd)
end

when i execute this stored procedure supplying pubs for database and jobs table, the seed and increment return null value. The IDENT_SEED() and IDENT_INCR() will only return the correct value if I will create this stored procedure in pubs database. But I want to have an application that should work in any database without creating the stored procedure in every database.

use northwind
exec dbo.usp_GenerateScriptTest 'pubs', 'jobs'


Anybody have an idea from what table and field i can get the value of identity seed and increment value?


Thank you very much