Thank you very much Cetin.


>>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