Tips and Tricks for our SQL Users. When should you utilize this query?
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]
