HOw do i use regular expressions in an exclude list?

stiej1977

Hi,

I want to compare two databases using the command line. but i want it to ignore all stored procs that begin with sp_MS.

How do i write this?

Thanks!

ajryan

Hi Stiej,

Here's how we do it:
differencesParam is a Differences instance obtained from Database.CompareWith(null, options)

Regex masterStoredProcedureRegex = new Regex("(\\[dbo\\]\\.\\[CopyToExport.*\\])|(\\[dbo\\]\\.\\[CopyToImport.*\\])", RegexOptions.Compiled);

            foreach (Difference difference in differencesParam)
            {
                if (difference.DatabaseObjectType == ObjectType.StoredProcedure)
                {
                    // filtering out export and import stored procedures
                    if (masterStoredProcedureRegex.IsMatch(difference.Name))
                    {
                        difference.Selected = false;
                    }
                    else
                    {
                        difference.Selected = true;
                    }
                }
                else
                {
                    difference.Selected = true;
                }
            }

stiej1977

many thanks for the reply.

but where would i place that code? how do i get sqlcompare.exe to use it?

thanks again.

Brian Donahue

Hi,

Using the pre-built command-line tool, the usage is similar, only the regular expression matches the object type, for instance

sqlcompare /s1:localhost /s2:localhost /db1:WidgetStaging /db2:WidgetProduction /exclude:Table:\[dbo\].\[Widget

The above will exclude any table beginning with the word Widget.

stiej1977

i prefer that method, so for stored procedures, what is the keyword in place of where you've got Table? Is it Proc?, StoredProc? SPROC? This is where i find the redgate help files a little on the thin side.

And are you missing the closing square brakect purposely?

Brian Donahue

I left out the close-bracket on purpose. Since bracket identifiers always exist in the object name as seen by SQL Compare, they're useful for begins with/ends with functionality in the regular expression. By the same logic, I could exclude a Table the ends with Prices like this: /exclude:Table:Prices\].

If you want to use /include /exclude options on stored procedures, you'd use /exclude:StoredProcedure:<regex>. To see the full list of objects that you can include or exclude, use the program help by running SQLCOMPARE.exe /? /v | more. all of the supported object types are then listed to the console.