Sql-server – How to query the names of encrypted stored procedures

sql serverstored-procedures

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.

Best Answer

SELECT name FROM sys.procedures
WHERE OBJECTPROPERTY([object_id], 'IsEncrypted') = 1;

Related Solutions

Sql-server – How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql

Something like

WHERE
   (@p1 IS NULL OR SomeCol1 = @p1)
   AND
   (@p2 IS NULL OR SomeCol2 = @p2)
   AND
   (@p3 IS NULL OR SomeCol3 = @p3)
   AND
   (@p4 IS NULL OR SomeCol4 = @p4)

This will probably run slower then using separate SELECTs separated by IF. However, you do have 16 permutation. So I'd probably combined both techniques:

IF (common combination of NULL parameters #1)
 SELECT ...
ELSE IF (common combination of NULL parameters #2)
 SELECT ...
ELSE 
 SELECT using WHERE above

T-sql – CASE ORDER BY with multiple columns, and different sort options

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.

Best Answer

Related Solutions

Sql-server – How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql

T-sql – CASE ORDER BY with multiple columns, and different sort options

Related Question