Filtering Not

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, --
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))
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
CAST(1 AS BIT) AS IsDefault,
CAST(0 AS BIT) AS QuotedIdentifier,
NULL AS ctext
FROM sys.default_constraints d WITH (NOLOCK)
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, --
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.
Kevin G. Boles
SQL Server MVP 2007-2012
Indicium Resources, Inc.

Best Answer

  • sam.blackburnsam.blackburn Posts: 224 Gold 2
    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.
    Software Developer
    Redgate Software


  • 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.
    Software Developer
    Redgate Software
  • 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?
    Kevin G. Boles
    SQL Server MVP 2007-2012
    Indicium Resources, Inc.
Sign In or Register to comment.