Search for text within Stored Procedure, View, Trigger and Function code

Was helping out a couple of colleagues to find a little piece of code from a plethora of stored procedures and triggers in the morning. Hence, thought of posting it here as part of my very infrequent Not-So-Hip-Snippets-Yet-Useful-At-Times series of code snippets.

This simple little snippet searched for pieces of text within a database object such as a stored procedure or trigger, and returns the name, type and last modified date of the object. You could search for pieces of text such as UPDATE dbo.Monsters or SET [Gender] = or some piece of code that you remember writing inside an SP or so.

 1 DECLARE @SearchPhrase varchar(100)
 2 DECLARE @Types TABLE ([type] varchar(5) COLLATE Latin1_General_CI_AS_KS_WS)
 3 
 4 -- ENTER TEXT TO SEARCH FOR:
 5 SET @SearchPhrase = ''
 6 
 7 -- The following statements include object types to be searched for.
 8 -- COMMENT OUT OBJECT TYPES THAT YOU DO NOT WISH TO BE INDLUDED IN THE SEARCH.
 9 INSERT INTO @Types
10 SELECT ''
11 UNION SELECT 'TR'-- Triggers
12 UNION SELECT 'P'-- Stored Procedures
13 UNION SELECT 'FN'-- Scalar Functions
14 UNION SELECT 'V'-- Views
15  
16 /* Search code */
17 SELECT
18     O.[name] AS [ObjectName],
19     O.[type_desc] AS [ObjectType],
20     O.[modify_date] AS [ModifiedDate]
21 FROM
22     sys.syscomments C
23     INNER JOIN sys.objects O
24     ON C.[id] = O.[object_id]
25 WHERE
26     C.[text] LIKE '%' + @SearchPhrase + '%'
27     AND O.[type] IN (SELECT [type] FROM @Types)

It’s a great way to search for a database object that you had written and cannot remember anymore, save for the little bit of code that you remember writing in it.

>Date of Latest Backup/Restore

>

My colleague who is a developer usually comes up with requests for little bits and pieces of database related information. This time, it was a request for the latest date that a particular database had been restored from a backup. T’was nothing but 10 minutes of BOL and coding, but lot of people out there might find it quite useful on some occasions. So here we go, the first in the series of Not-So-Hip-Snippets-Yet-Useful-At-Times…

A list of database on your instance are stored in the sys.databases table in the master database, while the backup and restore information is stored in the msdb database on several tables:

• For backups: dbo.backupset, dbo.backupmediaset, dbo.backupmediafamily
• For restores: dbo.restorehistory, dbo.restorefile, dbo.restorefilegroup

The database ID is not maintained in the listed tables, hence we would need to join the the sys.databases table with the required tables using the database names. This is the code to get a list of the latest backups performed against your databases:

SELECT
d.[name] AS 'Database Name',
ISNULL(CONVERT(varchar, MAX(b.[backup_finish_date]), 109), 'No backups') AS 'Last Backed up Date/Time',
ISNULL(b.[user_name], 'N/A') AS 'User', ISNULL(b.[type], '-') AS 'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
GROUP BY
d.[name], b.[user_name], b.[type]

While this is the code to get a list of the latest restore operations performed against your databases:

SELECT
d.[name],
ISNULL(CONVERT(varchar, MAX(r.[restore_date]), 109), 'No restores') AS 'Last Restored up Date/Time',
ISNULL(r.[user_name], 'N/A') AS 'User',
ISNULL(r.[restore_type], '-') AS  'Backup Type'
FROM
sys.databases d
LEFT OUTER JOIN msdb.dbo.restorehistory r ON d.[name] = r.[destination_database_name]
GROUP BY
d.[name], r.[user_name], r.[restore_type] 

You could stuff more details into this code, from the tables listed above to suit your requirements.