June 11, 2010 at 4:18 am
Hi,
Does anyone know if it's possible to insert the results of DBCC CHECKIDENT into a table?
Thanks
Dave
June 11, 2010 at 4:44 am
If you want to insert the IDENTITY value of a table into another table, there are other ways you can do it. One such way would be to use the IDENT_CURRENT() function
INSERT SomeTableName( TableName, IdentityValue )
SELECT 'TableName', IDENT_CURRENT( 'TableName' )
Hope this helps
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2010 at 4:49 am
Thanks for the reply, Unoforunatly that won't give me what I need.
I need to compare the results of the dbcc checkident on 767 tables. It looks like as part of an upgrade from 200 to 2008 the seeding has gone out on sync,
so ideally i'd want to try and insert the result from the dbcc command so far i've built this qury up. but it's only running the first table, does anyone know how to make it loop through all the tables for dbcc cmd?
DECLARE @sqlstring nvarchar(500);
DECLARE @tableNo int
DECLARE @name nvarchar(50);
DECLARE @ParmDefinition NVARCHAR(500);
Set @sqlstring =N'
SELECT @dbcccmdout =
''DBCC CHECKIDENT ('''''' + name +'''''');''
FROM
sys.objects
WHERE
OBJECTPROPERTY(OBJECT_ID(name),''TableHasIdentity'') = 1
AND
TYPE =''U''';
SET @ParmDefinition = N'@dbcccmdout varchar(1000) OUTPUT';
execute sp_executesql @sqlstring,@ParmDefinition,@dbcccmdout = @name output;
exec (@name)
June 11, 2010 at 5:11 am
You could do it in this way then
INSERTSomeTableName( TableName, IdentityValue )
SELECTname, ident_current( name )
FROMsys.objects
WHEREOBJECTPROPERTY( OBJECT_ID( name ),'TableHasIdentity' ) = 1
ANDTYPE = 'U'
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2010 at 5:12 am
Thanks for the reply,
The thing is I need the vaules current identity value and the current column value.
June 11, 2010 at 5:32 am
I am not sure but i think DBCC CHECKIDENT( 'TableName' ) always gives the same value for current column value and current identity value.
It changes only when you reseed it to a particular value. Does it change under any other condition..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2010 at 5:34 am
I'm afraid it doesn't
DBCC checkident ('tblxxxxxxxxxxxxx')
Checking identity information: current identity value '23935390', current column value '23935403'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 11, 2010 at 5:35 am
It changes when the database is restored from sql 200 to 2008,
I've run upgrade advisor and data purity checks and set the compatability mode to 100, so all bases covered - just can't explain why the seeding on the tables have gone to pot!
June 11, 2010 at 5:41 am
Well, thats a bit of a problem. I have no idea of how to solve this. Hopefully somebody will come up with a solution soon.
As for the problem in your code i think it is in this block
Set @sqlstring =N'
SELECT @dbcccmdout =
''DBCC CHECKIDENT ('''''' + name +'''''');''
FROM
sys.objects
WHERE
OBJECTPROPERTY(OBJECT_ID(name),''TableHasIdentity'') = 1
AND
TYPE =''U''';
It should actually be
Set @sqlstring =N'
SELECT @dbcccmdout = COALESCE( @dbcccmdout + '';'', '''' ) +
''DBCC CHECKIDENT ('''''' + name +'''''')''
FROM
sys.objects
WHERE
OBJECTPROPERTY(OBJECT_ID(name),''TableHasIdentity'') = 1
AND
TYPE =''U''';
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply