Filtering Not

TheSQLGuru

I filtered out a large database to just do table stuff. Then waited almost 20 minutes while this query ran:

SELECT sm.object_id AS id,
definition AS text,
1 AS colid,
(CASE WHEN OBJECTPROPERTY(so.object_id, N'IsReplProc')<>0 OR so.type = 'P' THEN 1 ELSE 0 END) AS number,
CAST(CASE WHEN sm.definition IS NULL THEN 1 ELSE 0 END AS bit) AS Encrypted, -- https://msdn.microsoft.com/en-us/library/ms175081.aspx
CONVERT(bit, CASE WHEN so.type = 'D' AND so.parent_object_id != 0 THEN 1 ELSE 0 END) AS IsDefault,
ISNULL(sm.uses_quoted_identifier, 0) AS QuotedIdentifier,
NULL AS ctext
FROM sys.sql_modules sm WITH (NOLOCK)
LEFT JOIN sys.objects so WITH (NOLOCK) ON so.object_id = sm.object_id
WHERE (so.type in ('P', 'V', 'TR', 'TA', 'IF', 'FN', 'TF', 'FS', 'FT')
OR (so.type = 'D' AND so.parent_object_id != 0)
OR sm.object_id IN (SELECT object_id FROM sys.triggers WITH (NOLOCK) WHERE parent_class=0))
UNION ALL
SELECT d.object_id AS id,
d.definition AS text,
parent_column_id AS colid,
0 AS number,
CAST(0 AS BIT) AS Encrypted, -- Not encryptable https://msdn.microsoft.com/en-us/library/ms173758.aspx
CAST(1 AS BIT) AS IsDefault,
CAST(0 AS BIT) AS QuotedIdentifier,
NULL AS ctext
FROM sys.default_constraints d WITH (NOLOCK)
UNION ALL
SELECT
p.object_id AS id,
p.definition AS text,
CAST(1 as int) AS colid,
procedure_number AS number,
CAST(CASE WHEN p.definition IS NULL THEN 1 ELSE 0 END AS bit) AS Encrypted, -- https://msdn.microsoft.com/en-us/library/ms179865.aspx
CAST(0 as bit) AS IsDefault,
m.uses_quoted_identifier AS QuotedIdentifier,
NULL AS ctext
FROM sys.numbered_procedures p WITH (NOLOCK)
LEFT JOIN sys.sql_modules m WITH (NOLOCK) ON p.object_id = m.object_id

ORDER BY id, colid, number

********************************

Please fix that.

sam.blackburn

AFAIK Prompt doesn't deal with differences between databases - do you mean Compare?

SQL Compare registers all the objects it can see - there is a workaround of connecting as a user that can only view the definition of a subset of objects in the database, although this could get messy if Compare fails to spot dependencies as I mentioned above.

You're sort-of correct about the performance effect decrypting encrypted objects, although that code path can also be exercised if a text object can't be read for other reasons such as permissions. If you don't care about text objects at all, disabling the option is a simpler solution than investigating possible causes of poor performance.

sam.blackburn

Filtering out objects only hides them from the UI. SQL Compare still needs to detect sprocs that depend on the tables you want to deploy, so that referenced tables can be renamed or schemabinding removed and put back if necessary.

A possible performance improvement would be to ensure the "Decrypt encrypted objects" option is disabled in SQL Compare.

TheSQLGuru

OK. Lets take a different tack then: Is there a way to use SQL Prompt such that you just see differences for objects/object types selected - without concern for anything else? I presume not, but it would be helpful in my current use case (finding differences between numerous supposedly-identical databases).

Regarding decrypt objects helping with performance, that should only make a difference if there actually are lots of encrypted objects, right?