LearnItFirst User Forum

/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This SQL script helps you identify the encvrypted and unencrypted objects in your databases. The first query
        allows you to filter to return only encrypted or unencrypted objects and the second query gives you a breakdown
        of how many encrypted SQL Server stored procedures you have, etc. If you want to know how to find encrypted stored
        procedures then use this SQL script.
   
    Versions: SQL Server 2005
   
    Creation Date: Sept 7, 2007

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
SELECT
SCHEMA_NAME(sp.schema_id) AS [Schema],
sp.name AS [Name],
sp.object_id AS [ID],
sp.create_date AS [CreateDate],
sp.modify_date AS [DateLastModified],
CAST(CASE WHEN smsp.definition IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
    ON smsp.object_id = sp.object_id
WHERE smsp.definition IS NULL -- This identifies an encrypted object
    AND sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
    AND sp.is_ms_shipped = 0

SELECT sp.type, sp.type_desc
    , COUNT(smsp.definition) AS UnencryptedObjects -- only non-null or unencrypted objects will be counted
    , COUNT(*)-COUNT(smsp.definition) AS EncryptedObjects
    , COUNT(*) AS Total
FROM sys.all_objects sp LEFT JOIN sys.sql_modules smsp
    ON smsp.object_id = sp.object_id
WHERE sp.type IN ('FN', 'IF', 'V', 'TR', 'PC', 'TF', 'P')
    AND sp.is_ms_shipped = 0
GROUP BY sp.type, sp.type_desc