Scanning all tables for highest ID

Question

Scanning all tables for highest ID

squeekz01

SSC-Addicted

Points: 445
More actions
January 26, 2014 at 3:02 am

#303706

Hi guys,
I am looking for help with writing what some may find as a basic query... I need to be able to scan all tables in my database for the ID column and then find the highest ID in all of the tables... The issue is that there will always be more and more tables in the database so I cannot add specific table names into the query...
Example of what I need to do:
Table 1 has a highest ID entry of 34
Table 2 has a highest ID entry of 56
Table 3 has no ID column
Table 4 has a highest ID entry of 14
The query will return 56
If anyone can help me and my noobie *** out... that would be much appreciated 🙂
Thanks,
Squeekz

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Search web for sp_msforeachtable. It will iterate through all user tables in the database and execute code you specify. You will need to test syscolumns joined to sysobjects to test each table for the ID columns and then execute a select max(id) from ? query against the ones that do. Easy-peasy-lemon-squeezy! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

squeekz01 SSC-Addicted Points: 445 More actions · Answer 2

Hi mate,

I have the following which is closer then what I had when I first posted... It still is not working though... It comes up with the error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'id'.

The column name 'id' exists in all tables except UAccounts which I am not using in my query.

My code is as follows:

sp_MSforeachtable @command1="SELECT MAX(id) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME <> 'UAccounts'"

Could you please help me fix this? I don't normally work with SQL Server and thus have no idea what I am doing... have just been asked to take a look for work.

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 3

squeekz01 (1/26/2014)
Hi guys,
I am looking for help with writing what some may find as a basic query... I need to be able to scan all tables in my database for the ID column and then find the highest ID in all of the tables... The issue is that there will always be more and more tables in the database so I cannot add specific table names into the query...
Example of what I need to do:
Table 1 has a highest ID entry of 34
Table 2 has a highest ID entry of 56
Table 3 has no ID column
Table 4 has a highest ID entry of 14
The query will return 56
If anyone can help me and my noobie *** out... that would be much appreciated 🙂
Thanks,
Squeekz

There's a system view built just for this kind of drill. The database you want to interogate has to be the current database and then the following will do what you want and a little bit more. The following will also demonstrate why one of the first things someone should do after learning about SELECT is to become familiar with all of the wonderful "sys" views and functions.

{EDIT} And, remember that all such things have privs to contend with. If your login isn't allowed to see certain things, this won't let you see them.

SELECT SchemaName = SCHEMA_NAME(CAST(OBJECTPROPERTYEX(sc.object_id,'SchemaId')AS INT))

,ObjectName = OBJECT_NAME(sc.object_id)

,ColumnName = sc.name

,DataType = TYPE_NAME (sc.system_type_id)

,MaxValue = sc.last_value

FROM sys.identity_columns sc

WHERE OBJECTPROPERTYEX(sc.object_id,'IsTable') = 1

ORDER BY MaxValue DESC, ObjectName

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

>> FROM sys.identity_columns

Thanks for that nugget Jeff - almost 45000 man hours working with SQL Server and still learning!! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004367 More actions · Answer 5

TheSQLGuru (1/26/2014)
>> FROM sys.identity_columns
Thanks for that nugget Jeff - almost 45000 man hours working with SQL Server and still learning!! :w00t:

I'm right there with you, Kevin. I learn something new about SQL Server just about every day. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

squeekz01 (1/26/2014)
Hi guys,
I am looking for help with writing what some may find as a basic query... I need to be able to scan all tables in my database for the ID column and then find the highest ID in all of the tables... The issue is that there will always be more and more tables in the database so I cannot add specific table names into the query...
Example of what I need to do:
Table 1 has a highest ID entry of 34
Table 2 has a highest ID entry of 56
Table 3 has no ID column
Table 4 has a highest ID entry of 14
The query will return 56
If anyone can help me and my noobie *** out... that would be much appreciated 🙂
Thanks,
Squeekz

Try this

USE [yourdb]

EXEC sp_MSforeachtable @command1 = 'PRINT ''Table ? seed information'' + CHAR(10)',

@command2 = 'DBCC CHECKIDENT ("?", NORESEED)', @command3 = 'PRINT '''' + CHAR(10) + CHAR(13)'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Jeff Moden (1/26/2014)
squeekz01 (1/26/2014)
Hi guys,
I am looking for help with writing what some may find as a basic query... I need to be able to scan all tables in my database for the ID column and then find the highest ID in all of the tables... The issue is that there will always be more and more tables in the database so I cannot add specific table names into the query...
Example of what I need to do:
Table 1 has a highest ID entry of 34
Table 2 has a highest ID entry of 56
Table 3 has no ID column
Table 4 has a highest ID entry of 14
The query will return 56
If anyone can help me and my noobie *** out... that would be much appreciated 🙂
Thanks,
Squeekz
There's a system view built just for this kind of drill. The database you want to interogate has to be the current database and then the following will do what you want and a little bit more. The following will also demonstrate why one of the first things someone should do after learning about SELECT is to become familiar with all of the wonderful "sys" views and functions.
{EDIT} And, remember that all such things have privs to contend with. If your login isn't allowed to see certain things, this won't let you see them.
SELECT SchemaName = SCHEMA_NAME(CAST(OBJECTPROPERTYEX(sc.object_id,'SchemaId')AS INT))
,ObjectName = OBJECT_NAME(sc.object_id)
,ColumnName = sc.name
,DataType = TYPE_NAME (sc.system_type_id)
,MaxValue = sc.last_value
FROM sys.identity_columns sc
WHERE OBJECTPROPERTYEX(sc.object_id,'IsTable') = 1
ORDER BY MaxValue DESC, ObjectName
;

This works as well:

SELECT SchemaName = object_schema_name(sc.object_id)

,ObjectName = OBJECT_NAME(sc.object_id)

,ColumnName = sc.name

,DataType = TYPE_NAME (sc.system_type_id)

,MaxValue = sc.last_value

FROM sys.identity_columns sc

WHERE OBJECTPROPERTYEX(sc.object_id,'IsTable') = 1

ORDER BY MaxValue DESC, ObjectName

Tested it on SQL Server 2012 SP1 and SQL Server 2005 SP4.

sneumersky SSCertifiable Points: 7667 More actions · Answer 8

Does this query assume the latest identity value is the greatest?

I once had a BI client that reached the highest integer value possible in an identity column. This client then, instead of modifying the column to a bigint, reseeded the identity column to the lowest negative value possible (in order to buy them time and put off the inevitable task of changing the column to a bigint).

I'm thinking in this specific case last_value wouldn't necessarily be the greatest value?

P.S. I did NOT condone what this client did with the identity column 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

sneumersky (1/27/2014)
Does this query assume the latest identity value is the greatest?

No the query that Lynn posted has nothing to do with when the value was created. It is looking for the largest value which is what you said you wanted.

There is nothing inherent in an identity that means that the largest value is the most recent. Identity insert can be turned on and off, the seed order can reduce the next value, the identity can be reseeded etc, etc, etc...

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Sean Lange (1/27/2014)
sneumersky (1/27/2014)
Does this query assume the latest identity value is the greatest?
No the query that Lynn posted has nothing to do with when the value was created. It is looking for the largest value which is what you said you wanted.
There is nothing inherent in an identity that means that the largest value is the most recent. Identity insert can be turned on and off, the seed order can reduce the next value, the identity can be reseeded etc, etc, etc...

Actually, all my query did was make a change to Jeff's query by changing this:

SCHEMA_NAME(CAST(OBJECTPROPERTYEX(sc.object_id,'SchemaId')AS INT))

to this:

object_schema_name(sc.object_id)

sneumersky SSCertifiable Points: 7667 More actions · Answer 11

Although I was not the original topic posting, I guess I may have misinterpreted something. My fault.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Jeff's and Lynn's queries return the last value, which, as was pointed out, isn't necessarily the largest. Depending on what the exact requirement here is, it may be worth considerting a single sequence object (new in SQL Server 2012) instead of an identity column in each table. It has the same caveats about values possibly being altered after insertion and the sequence being cycled, but it may make things easier to manage if you need to know what the latest value is.

John

sneumersky SSCertifiable Points: 7667 More actions · Answer 13

sneumersky

SSCertifiable

Points: 7667

January 27, 2014 at 10:04 am

#1683630

Got it. Thanks.