HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to query the names of encrypted stored procedures?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
storedproceduresthequeryencryptednameshow

Problem

What query will identify the names of stored procedures which are encrypted? Here's what I got so far -

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
-- need another condition here to identify just the encrypted SPROC's
order by ROUTINE_NAME asc


Thanks in advance.

Solution

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_DEFINITION is null
order by ROUTINE_NAME asc


An encrypted Stored Procedure will have a NULL ROUTINE_DEFINITION.

Code Snippets

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and ROUTINE_DEFINITION is null
order by ROUTINE_NAME asc

Context

StackExchange Database Administrators Q#15100, answer score: 3

Revisions (0)

No revisions yet.