How do I compare large stored procedures?
This question exists on two levels:


Comparing between two databases:

In this context you do not need to worry about length (outside of perhaps using DATALENGTH as a quick indicator of there being a difference and hence no need to compare the actual string values). Here you just need to make sure that the definitions are identical, and that does not require SUBSTRING, but it does require adding COLLATE Latin1_General_100_BIN2 to the comparison to ensure that you catch differences in case, etc that otherwise evaluate as being equal (using an everything-sensitive Collation, such as anything ending with _CS_AS_WS_KS[_SC], can't catch differences when equality is determined by combining characters and/or linguistic rules).

Within a particular environment, an object should be identical across all databases where it is deployed to. There can't be any differences due to scripting since none of the definitions are being compared to the actual source.

While comparisons on this level are easier (since you need not worry about white-space differences), they can also be misleading if all definitions are identical, yet they are all different from the source definition in the exact same way.
Comparing between database and source script

In this context you do need to worry about length and white-space variation depending on how your source object scripts are written and how they are packaged up for deployment. Here T-SQL is not going to help you as there are too many variations in what could be different yet still be "the same".

This is where several 3rd party tools come into play and you should choose one of them. They can be configured to ignore certain white-space as well as include additional properties and sometimes even permissions.

If you want to know if there are differences in Production that should either be removed OR factored into the source definition, you can only determine that by comparing to the source object script.

If you do not have source scripts for each object, then you absolutely need to do this and now is as good of a time as any to start.

If you do not have your source scripts in some sort of version control (Git, SVN, TFS, anything..), then you absolutely need to do this and now is as good of a time as any to start.




On a related note, the equation for the max size (in bytes, not "characters") is correct:

SELECT POWER(CONVERT(BIGINT, 2), 31) - 1
-- 2147483647


That value matches the max size of an INT as noted here. Since NVARCHAR is UTF-16, that is either 2 or 4 bytes per Code Point (a "character" is comprised of 1 or more Code Points, such as when using combining characters), hence the max "length" of a string is half of that, assuming that all "characters" are of the single, 2-byte variety (and they usually are). Meaning:

SELECT 2147483647 / 2
-- 1073741823


So the max amount of "characters" in an NVARCHAR(MAX) column is 1,073,741,823.

HOWEVER, this is irrelevant for SUBSTRING since it doesn't error in T-SQL if requested to return more characters than are in the input string. For example:

SELECT SUBSTRING('A', 1, 100);
-- A


So you can just put an arbitrary high value as the 3rd parameter.