General information
Forum:
Microsoft SQL Server
Title:
Getting Identity Seed value and Increment Value
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only