March 30, 2011 at 12:03 pm
What's the quickest way to find the maximum value of the Primary Key column for all tables? Can it be done without a cursor?
March 30, 2011 at 2:14 pm
Yes, it can be done without a cursor or loop. Whether you should use dynamic SQL will depend on what will consume the results. Is this a one-off request or will you need to find and use the max values as part of an application? Will you need this at different times, and if so, will it need to include results for tables added to the system since the last time it ran?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 30, 2011 at 2:51 pm
It's going to be part of a stored procedure, so it'll be used more than once. I'm merging two databases together and using this to store the value of the PK of one database so I can align the PKs of both databases.
March 30, 2011 at 6:06 pm
Have you looked into tools like Red Gate SQL Data Compare or the Data Compare functionality within Visual Studio 2010 to see if it will do what you want?
If those don't measure up then here is how I would approach it:
1. Find all the PKs in your system and their associated columns. This should help get you started:
SELECT o.name,
i.name,
ic.key_ordinal,
c.name
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
JOIN sys.index_columns ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE i.is_primary_key = 1 ;
2. Generate the SQL to select the max value of the column involved in your PK using the metadata from step 1 and store it in a variable. Do you have any composite PKs or character-based PKs?
3. Execute the SQL in your variable from step 2 and capture the results in a table.
None of this implies looping, i.e. you should be able to do this without a cursor or while loop.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply