I want to compare stored procedures that should be identical in several different databases, by retrieving and comparing the definitions from sys.sql_modules.
Currently I'm thinking about procedures which are "identical" except that one or other copy has blank lines or other white space at the start or end. These seem to be added by making a script of a procedure. I'd like to remove the blank lines and such and compare what's left. So I could do:
IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) ) SET @definition1 = SUBSTRING(@definition1, 3, 1048576)
That will work if the length of the procedure is less than 1048576 bytes. But what is the actual limit on length - if any?
I suppose actually I should ask: what is a good way to compare procedures? (Apart from "Buy the SQL Compare utility from Red Gate.")
(revised) I just realised that sys.sql_modules.definition has data type nvarchar(max) and that has a specified length... in bytes, POWER(2, 31)-1, except that that formula doesn't work ;-) In nchar characters, no more than POWER(2, 30)-1.
So this ought to do it:
IF ( LEFT(@definition1, 2) = NCHAR(13) + NCHAR(10) ) SET @definition1 = SUBSTRING(@definition1, 3, 2147483647)
Yes, that is 2^31-1, it ought to be 2^30-1, but as long as it's bigger than @definition1 is or can be, I get away with it. Until they increase "max" to 2^63-1 bytes. Before there was "max", I used 8000.
And maybe I should include double-checking the DATALENGTH() in case it accidentally gets truncated to the first 8000 bytes somewhere.
As for your wise words about good code management to not have this need in the first place - useful ammunition, thank you (now to extract the bullets from my own hide). I recognise the value of methodical working practices, even when they have to apply to me, as well. But a problem is management who let's just say hypothetically just want a report or a result produced -now-, and who have been getting and expecting service like that for many years - and now they've learned to call it "Agile".