home
training courses
why choose us?
solutions
support
company
LearnItFirst User Forum
Welcome Guest
Search
|
New Posts
|
Members
|
Log In
|
Register
SQL Server Forum - LearnItFirst.com
»
SQL Server Scripts, Code Samples and SSMS Custom Reports
»
SQL Server 2005/2008
»
SQL Script to Return All Encrypted and Unencrypted Objects
SQL Script to Return All Encrypted and Unencrypted Objects
Options
Previous Topic
·
Next Topic
Scott Whigham
Posted:
Friday, September 07, 2007 7:39:24 AM
Rank: Super Mod
Joined: 3/20/2006
Posts: 460
Points: 1,002
Where do you live?: 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
Back to top
Users browsing this topic
Guest
Forum Jump
SQL Server Database Administration
- General SQL Database Question & Answer
- Backup, Recovery and Disaster Recovery
- SQL Server Security
- Integration Services (SSIS) and DTS
Transact-SQL Programming
- DML (SELECT, INSERT, UPDATE, DELETE) Questions
- Stored Procedures, Triggers, & Functions
SQL Server Scripts, Code Samples and SSMS Custom Reports
- All SQL Server Versions
- SQL Server 2005/2008
- SQL Server Management Studio Custom Reports
Customer Service
- Video Requests
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.
SQL Server 2005 DBA Training Videos
SQL Server 2008 DBA Training Videos
Watch this topic
RSS Feed
Email this topic
Print this topic
Threaded
Normal