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".

  • Why are you looking to compare them? Does an extra line of whitespace matter at all? Commented May 25, 2017 at 10:33
  • 4
    Schema compare is available with SQL Server Data Tools. The VS community edition is free (visualstudio.com/vs/ssdt).
    – Dan Guzman
    Commented May 25, 2017 at 10:41
  • 1
    Diff tools are for doing exactly that, comparing files. Commented May 25, 2017 at 11:01
  • 2
    One scenario is to test whether procedures in databases bistaging01, bistaging02, 03, up to 32 are the same as each other and the same as bistagingDev, or whether a developer has stuck a code hotfix straight into bistaging06, again. Then I have to sort out the mess. So it's not so much "Version Control" as "Combat". Commented May 25, 2017 at 11:21
  • 3
    And it sounds like the problem is not about comparing at all, it's that you have rogue developers who are allowed to do things they shouldn't? Commented May 25, 2017 at 12:32

4 Answers 4


This question exists on two levels:

  1. 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.

  2. 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:

-- 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:

-- A

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

  • Some characters, e.g. 🐿️, have the data length of 6 bytes when stored as nvarchar.
    – Andriy M
    Commented May 26, 2017 at 14:11
  • @AndriyM UTF-16 does not allow for that. The test you provided is misleading as it has a 2-byte, 0-width / non-printable character following Mr Chipmunk. I copied and pasted that test string into a new test and provided some additional investigation to show why you are seeing a DataLength of 6 here: dbfiddle.uk/… Commented May 26, 2017 at 14:47
  • @AndriyM To be fair, I updated my wording to hopefully clarify between Code Points and "characters" (a more nebulous concept). When working with combining characters and variants (such as your Emoji test case), the displayed "character" can be comprised of any number of Code Points, but those sequences do not combine into a single, composite entity. On the other hand, Supplementary Characters, comprised of two 2-byte Code Points, are addressable as a single unit (as long as you use a Collation ending with _SC). Point being, the chipmunk emoji is a character plus an optional modifier. Commented May 26, 2017 at 15:45
  • I've now found this page, according to which the chipmunk character proper (which is encoded as a 4-byte sequence in UTF-16) may be followed by either U+FE0E or U+FE0F (which was my case). My confusion is resolved, thanks :)
    – Andriy M
    Commented May 26, 2017 at 20:53

I'd suggest to take a look at some third party tool, have a look at Apex SqlDiff.

We use it to synchronize two identical databases in production.


Other than RedGate's tools (which I do recommend highly), I usually use Notepad++ and its COMPARE plugin. It lets you compare two files (of any language) and allows for settings changes on white space, case sensitivity, etc.

http://www.technicaloverload.com/compare-two-files-using-notepad/ https://sourceforge.net/projects/npp-compare/ https://notepad-plus-plus.org/

However, as stated above, it seems like you have other issues than just comparing two files.


As suggested in the comments, SQL Server Data Tools has a schema compare tool. Its free, configurable and simple to use. It runs from inside Visual Studio or from a Command line using SQLPackage.exe, some details of which I blogged here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.