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 0 comments links to this post

I missed this on Friday so I wanted to write about it today. The "latest" beta from MSFT is out: SQL Server 2008 CTP 4 and you can download it here. What's different? Well first off, it starts off all wrong: if you want to play, you must first install Virtual Server 2005 R2! If you do not have Virtual Server installed you can get it free from www.microsoft.com/virtualserver.

But I don't want Virtual Server - I use VMWare. I don't like Virtual Server. Bah!!!!!

This is an odd choice, isn't it? The past CTPs have all been downloadable setup files that we could test on any system, any OS, any configuration, etc but this new CTP is only testable on Microsoft's pre-configured server. I haven't seen/read any blog posts about this decision - hopefully someone can post a comment including what the reason behind this choice is/was.

# posted by Scott Whigham @ 1:09 PM 1 comments links to this post