>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

Qualify the name in IDENT_SEED and IDENT_INCR.

 IDENT_SEED (''' + @TargetDB + '..'+ @TableName +''') as Seed,
 IDENT_INCR (''' + @TargetDB + '..'+ @TableName +''') as Increment

PS: I'd also include schema.
Cetin