CREATE BLOG dbo.SqlServer

Today I was dealing with a database that has encrypted objects - functions and stored procedures - but not all of the objects were encrypted and I needed to be able to quickly identify which objects were encrypted and which weren't. This is a database that needs everything encrypted so I wrote these scripts (below) that help you find all encrypted objects in a SQL Server database. Check it out -

/*
    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

# posted by Scott Whigham @ 7:44 AM