October 14, 2016 at 8:14 am
Hi
I need to find the current identity value compared to the current column value of tables with identity fields - in particular where the current column value is less than the current identity value.
I have run
DBCC CHECKIDENT (tablename)
which returns the required info and I can modify this code to use the MS_Foreachtable stored procedure.
The problem is that I only really want to see output from this DBCC command where the current column value is less than the IDENTITY value.
Is there a way of writing such a query that will give me only the tables where the current column value is less than the current identity value (displaying the identity value, and the current id value)
October 16, 2016 at 1:24 pm
The only way I can think of is to loop through the tables and check each one
CREATE TABLE #TableList (
ID INT IDENTITY(1,1)
, object_id INT PRIMARY KEY CLUSTERED
, SchemaName SYSNAME
, TableName SYSNAME
, ColumnName SYSNAME
, CurrentIdentity BIGINT
, CurrentMaxValue BIGINT
);
INSERT INTO #TableList ( object_id, SchemaName, TableName, ColumnName, CurrentIdentity )
SELECT
ic.object_id
, SchemaName = SCHEMA_NAME(t.schema_id)
, TableName = t.name
, ColumnName = ic.name
, CurrentIdentity = CAST(ic.last_value AS BIGINT)
FROM sys.identity_columns ic
INNER JOIN sys.tables t ON ic.object_id = t.object_id
WHERE t.type = 'U';
DECLARE @ID INT = 0;
DECLARE @sqlCmd VARCHAR(2000)
SELECT @ID = MIN(ID) FROM #TableList WHERE ID > @ID;
WHILE (@ID > 0)
BEGIN
SELECT @sqlCmd = 'UPDATE #TableList SET CurrentMaxValue = (SELECT CAST(MAX([' + ColumnName + ']) AS BIGINT) FROM [' + SchemaName + '].[' + TableName + ']);'
FROM #TableList
WHERE ID = @ID;
PRINT @sqlCmd;
EXEC (@sqlCmd);
SELECT @ID = MIN(ID) FROM #TableList WHERE ID > @ID;
END;
SELECT *
FROM #TableList
WHERE CurrentIdentity <= CurrentMaxValue;
DROP TABLE #TableList;
October 16, 2016 at 4:23 pm
PearlJammer1 (10/14/2016)
HiI need to find the current identity value compared to the current column value of tables with identity fields - in particular where the current column value is less than the current identity value.
Unless someone is doing something with negative increments... Since all values in the "current column" should, in fact, be less than the current identity value, I have to ask why you are trying to do this.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2016 at 7:05 am
Hi Jeff
It is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.
October 17, 2016 at 7:26 am
PearlJammer1 (10/17/2016)
Hi JeffIt is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.
That is not an uncommon situation. If you do an insert and it gets rolled back, the IDENT that was generated is is not re-used, unless you reseed it with DBCC CHECKIDENT (tablename, RESEED [, new_reseed_value ])
October 17, 2016 at 7:40 am
PearlJammer1 (10/17/2016)
Hi JeffIt is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.
So, just to be clear, you're saying that if you do an INSERT on a table that has an IDENTITY column, the value for that IDENTITY column for that newly inserted row appears as a number less than the maximum value already in that column?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2016 at 8:10 am
Jeff Moden (10/17/2016)
PearlJammer1 (10/17/2016)
Hi JeffIt is because we have a situation where some tables appear to have id values less than the current identity value which will get auto generated on next insert.
So, just to be clear, you're saying that if you do an INSERT on a table that has an IDENTITY column, the value for that IDENTITY column for that newly inserted row appears as a number less than the maximum value already in that column?
I have to admit I'm curious about this as well.
October 17, 2016 at 3:56 pm
What has happened (and I don't yet fully understand why but will discuss with our dev team) is that for example, we have an ID field on a table with IDENTITY(1,1). For arguments sake, the next IDENTITY returned from the query:
SELECT IDENT_CURRENT('schema.mytable') AS CurrentIdentityVal,max(idcolumn) as CurrentMaxVal FROM schema.mytable
Returns:
CurrentIdentityVal = 110
CurrentMaxVal = 100
So I have been asked to produce a query that lists all the tables with their Current IDENTITY Value compared to the actual max value stored in the table.
Does this clear things up ?
October 17, 2016 at 4:02 pm
Thanks for the input on this Des
I have modified the query as the I was getting NULL in all of the CurrentMaxValue columns
I came up with this
Create table #MyTable
(
ID INT IDENTITY(1,1),
[object_id] int,
SchemaName varchar(75),
TableName varchar(75),
ColumnName varchar(75),
last_value int,
CurrentMaxValue BIGINT
)
CREATE Table #Results
(
[object_id] int,
SchemaName varchar(75),
TableName varchar(75),
ColumnName varchar(75),
last_value int,
CurrentMaxValue BIGINT
)
DECLARE @ID INT = 0;
DECLARE @sqlCmd VARCHAR(2000)
INSERT INTO #MyTable ([object_id],SchemaName,TableName,ColumnName,last_value)
select st.object_id,SchemaName = SCHEMA_NAME(st.schema_id), st.name as TableName, ic.name as ColumnName, last_value = CAST(ic.last_value AS BIGINT)
from sys.tables st
INNER JOIN sys.identity_columns ic
on st.object_id = ic.object_id
where ic.is_identity = 1
DECLARE @sql VARCHAR(MAX)
SELECT @ID = MIN(ID) FROM #MyTable WHERE ID > @ID;
WHILE @ID>0
BEGIN
SElect @sql = 'UPDATE #MyTable SET CurrentMaxValue = (SELECT CAST(MAX([' + ColumnName + ']) AS BIGINT) FROM [' + SchemaName + '].[' + TableName + ']);'
from #MyTable
WHERE ID=@ID
exec (@SQL)
insert into #Results
select top 1 [object_id],SchemaName,TableName,ColumnName,last_value,CurrentMaxValue
FROM #MyTable
delete TOP (1) from #MyTable
SELECT @ID = MIN(ID) FROM #MyTable WHERE ID > @ID
END
SELECT * FROM #Results
where CurrentMaxValue <> last_value
drop table #MyTable
DROP TABLE #Results
October 17, 2016 at 6:31 pm
PearlJammer1 (10/17/2016)
What has happened (and I don't yet fully understand why but will discuss with our dev team) is that for example, we have an ID field on a table with IDENTITY(1,1). For arguments sake, the next IDENTITY returned from the query:
SELECT IDENT_CURRENT('schema.mytable') AS CurrentIdentityVal,max(idcolumn) as CurrentMaxVal FROM schema.mytableReturns:
CurrentIdentityVal = 110
CurrentMaxVal = 100
So I have been asked to produce a query that lists all the tables with their Current IDENTITY Value compared to the actual max value stored in the table.
Does this clear things up ?
There's nothing wrong with that particular scenario. It just means 1 of several things.
1. Rows were deleted from the table.
2. One of more inserts failed and were rolled back, which still "consumes" an IDENTITY value.
3. The SQL Server Service was restarted, which sometimes causes IDENTITY values to be lost because of the supposed pre-optimization SQL Server does by creating new IDENTITY values behind the scenes ahead of time. Such pre-created IDENTITY values are lost "forever".
Now, if things were turned around like this...
CurrentIdentityVal = 100
CurrentMaxVal = 110
... THEN you'd have a real problem on your hands. That would mean that someone has been messing around with the identity seed for the table or someone has been using SET IDENTITY INSERT ON.
IDENTITY values are not guaranteed to be sequential in an IDENTITY column for the reasons I just stated.
For what you have (and thank you VERY much for clarifying with code), there is no non normal problem. I personally think it's a total waste of time to go through the drill you've been asked to go through unless you have a known issue with missing data.
If they insist, the following code will find the anomalies you've mentioned. It could take quite a while to execute. If it returns nothing, then no such anomalies exist. If you want proof, add two dashes in front of the word HAVING in the dynamic SQL to return the values for ALL identity columns found in the database. Either way, you should charge them $100 USD and send me half. 😉
/**********************************************************************************************************************
Purpose:
For the current database, find all tables having an IDENTITY column, compare the value of IDENT_CURRENT against the
MAX value in the column, and report those tables and values that aren't an exact match.
Revision History:
Rev 00 - 17 Oct 2016 - Jeff Moden
Initial creation and unit test.
REF: http://www.sqlservercentral.com/Forums/Topic1826014-3411-1.aspx
Rev 01 - 18 Oct 2016 - Jeff Moden
Add QUOTENAME() to the REPLACEs to cover the eventuality of names with dashes, spaces, and other horrors. ;-)
**********************************************************************************************************************/
--===== Declare the code accumulator variable
DECLARE @sql VARCHAR(MAX)
;
--===== Find every IDENTITY column and build the dynamic SQL to list the
-- IDENT_CURRENT value and the MAX value in the column.
-- This uses a <<TOKEN>> replacement method instead of a bazillion quotes and plus signs.
SELECT @sql = ISNULL(@SQL + ' UNION ALL','')
+ REPLACE(REPLACE(REPLACE(REPLACE('
SELECT QualifiedObjectName = "<<SchemaName>>.<<ObjectName>>"
,CurrentIdentityVal = IDENT_CURRENT("<<SchemaName>>.<<ObjectName>>")
,CurrentMaxVal = MAX(<<ColumnName>>)
FROM <<SchemaName>>.<<ObjectName>>
HAVING IDENT_CURRENT("<<SchemaName>>.<<ObjectName>>") <> ISNULL(MAX(<<ColumnName>>),-2147483648)
' -- These are the other end of the REPLACEs.
,'"','''')
,'<<SchemaName>>',QUOTENAME(OBJECT_SCHEMA_NAME(object_id))) --Rev 01
,'<<ObjectName>>',QUOTENAME(OBJECT_NAME(object_id))) --Rev 01
,'<<ColumnName>>',QUOTENAME(name)) --Rev 01
FROM sys.columns
WHERE is_identity = 1
AND OBJECT_SCHEMA_NAME(object_id) <> 'sys'
AND OBJECTPROPERTY(object_id,'IsTable') = 1
;
--===== Display ALL the code to be executed up to the length limit of the server XML max length.
-- This returns a clickable XML "Cell" when in the GRID MODE for result sets in SSMS.
SELECT SQLCode = (SELECT REPLACE(CAST('--' + CHAR(10) + @sql + CHAR(10) AS VARCHAR(MAX)), CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
AS [processing-instruction(SQLCode)]
FOR XML PATH(''), TYPE)
;
--===== Uncomment this once you've determined that the dynamic SQL is actually safe to run.
--EXEC (@SQL)
;
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2016 at 2:12 am
Hi Jeff
As ever, your thoughts are greatly appreciated so thanks for your input on this. I have posted the script that I finally used to return what i am after further up in the post - thanks to Des for his input on that as well.
I'll keep your script safe and run it on my laptop against the Adventureworks database to get a feel for how it works. I didn't know that about the identity values being lost sometimes if you restart sql server - that sounds like a worrying bug to me !
As you say the real problem would be if the values returned where the other way around - thankfully they are not.
Much appreciated
October 18, 2016 at 8:47 pm
PearlJammer1 (10/18/2016)
Hi JeffAs ever, your thoughts are greatly appreciated so thanks for your input on this. I have posted the script that I finally used to return what i am after further up in the post - thanks to Des for his input on that as well.
I'll keep your script safe and run it on my laptop against the Adventureworks database to get a feel for how it works. I didn't know that about the identity values being lost sometimes if you restart sql server - that sounds like a worrying bug to me !
As you say the real problem would be if the values returned where the other way around - thankfully they are not.
Much appreciated
First, thank you for your always-kind feedback. Much appreciated.
I made a small change to the code to cover the eventuality of a naming problem where the schema name, object name, or column name might be malformed (has a dash or space in it or starts with a $, etc, etc) or a reserved word. I've updated the code above. Search for "Rev 01" in the code to see that I only added QUOTENAME() to the operands in the REPLACE functions.
Also, I ran this on two of my databases on my production box. Both of the databases have more than 1,300 tables each, some without IDENTITY columns. It never took more than about 12 seconds to execute and that's even while a backup is executing on the database the code was running against.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply