SQL Tips and Tricks: Find All Query

Tips and Tricks for our SQL Users. When should you utilize this query?

| Aspect Consulting | 27 November 2020

Find All Query

The Problem

Your boss has asked you to make changes to an application that will affect an MS SQL Server database, and first needs to know the impact analysis of those changes before any work is performed.

The Solution

Utilize this simple Find All query to help you find all impacted objects in your database with minimal effort.

Let’s say you need to find all occurrences of “status” in your database. Click to view query syntax that will find all objects (tables, stored procedures, views, functions, triggers, keys, and default values) that include the string “status” somewhere in the name or contents of the object, and provide a count of those occurrences.

DECLARE @searchString varchar(500)

 

SET @searchString = ‘status’

SELECT rs.[Name]

, rs.[Type]

, rs.[xtype]

COUNT([TextMatch]) as ‘Number of References’

FROM   (

SELECT so.[Name]

, so.[Type]

, so.[xtype]

, so.[Name] as ‘TextMatch’

FROM   sysobjects so

WHERE  so.[name] IS NOT NULL

AND    so.[Name] LIKE ‘%’ + @searchString + ‘%’

AND    (   so.[Type] = ‘U’   — User Table

OR so.[Type] = ‘P’   — Stored Procedure

OR so.[Type] = ‘V’   — View

OR so.[Type] = ‘FN’  — User Defined Function

OR so.[Type] = ‘TR’  — Trigger

OR so.[Type] = ‘K’   — Primary Key

OR so.[Type] = ‘F’   — Foreign Key

OR so.[Type] = ‘DF’  — Default Value

)

UNION

SELECT so.[Name]

, so.[Type]

, so.[xtype]

, sc.[text] as ‘TextMatch’

FROM   sysobjects so

JOIN   syscomments sc

ON     so.[id] = sc.[id]

WHERE  so.[name] IS NOT NULL

AND    sc.[text] LIKE ‘%’ + @searchString + ‘%’

AND    (   so.[Type] = ‘U’   — User Table

OR so.[Type] = ‘P’   — Stored Procedure

OR so.[Type] = ‘V’   — View

OR so.[Type] = ‘FN’  — User Defined Function

OR so.[Type] = ‘TR’  — Trigger

OR so.[Type] = ‘K’   — Primary Key

OR so.[Type] = ‘F’   — Foreign Key

OR so.[Type] = ‘DF’  — Default Value

)

) rs

GROUP BY rs.[Name], rs.[Type], rs.[xtype]

ORDER BY rs.[Type], rs.[name]

 

 

All Blog Articles
Tips & Tricks
Determine the Name of SharePoint Application Database by SQL Server:

Use SQL Server Client Network Utility The Problem: SharePoint is, of course, a monster of an application platform with many configuration quirks.  As a DBA supporting a content database utilized by a...

21 November 2018

Tips & Tricks
Collect Cross-Environment Statistics Using Simple Powershell Process:

Using Simple Powershell Processes Your DBA is receiving complaints about a slow server at random periods throughout the week.  How do you assess the environment and troubleshoot performance issues at...

14 November 2018

Tips & Tricks
Tips for a Successful Video Interview in 2022

Congratulations! You Have a Video Interview. Congratulations on your new employment journey! If you’re here, we’ll assume you’re either searching for a new position or you have already scored an...

17 March 2022

footerLogo

610-783-0600 info@aspect-consulting.com

20140 Valley Forge Circle King of Prussia, PA 19406

Resources

Aspect Consulting, Inc © 2022      All rights reserved.

Privacy Policy        Title of the document Legal Disclaimer