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.