Member of the LearnItFirst.com Video Training Network | LearnSqlServer.com | SQL SSIS Training | SQL Programming Tutorials |
LearnSqlServer.com Forums LearnSqlServer.com
Welcome Guest Search | New Posts | Members | Log In | Register

SQL Script to Return All Encrypted and Unencrypted Objects Options
Scott Whigham
Posted: Friday, September 07, 2007 7:39:24 AM


Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX
Code:
/*
    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
Users browsing this topic
Guest


Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
     
Don't Forget!
LearnItFirst.com
Don't Forget!
LearnWindows2003.com
 
Home | About Us | Support | Contact Us | Privacy | Site Map | Blogs Blogs Refer a Friend and Get a Free Subscription!
© Copyright 2004-2007 LearnItFirst.com LLC. All rights reserved. All trademarks remain the property of their respective owners.
This site is not affiliated in any way with the Microsoft Corporation.