SQL Server - How to Search for Objects in a database

In this post, we will see how to search for objects in our SQL server databases, using a simple Stored Procedure called SearchInAll.
In the world of database management, searching through various database objects for specific text can be a tough task, especially in large and complex databases. Whether it’s for debugging, auditing, or just general maintenance, having a tool that can quickly search across stored procedures, views, functions, and tables is incredibly valuable.
This is where the SearchInAll Stored Procedure can help us.

The SearchInAll is a powerful SQL scripts that helps us to search for a specific string across various types of database objects like stored procedures, views, functions, and table column names.
The procedure takes a string as an input parameter and searches for it across:

Stored Procedures: Checking both the name and the definition of each stored procedure.
Views: Searching within the definitions of all views.
Functions: Looking through all functions, both in their names and definitions.
Table Column Names: Scanning through all tables to find any column names containing the specified string.

Let’s see how it works!
First of all, we define a Docker-compose file to create a docker container with SqlServer:
[DOCKER-COMPOSE.YML]

version: '3.8'

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      SA_PASSWORD: "2024p@ss"
      ACCEPT_EULA: "Y"
    ports:
      - "1433:1433"
    volumes:
      - sql_data:/var/opt/mssql

volumes:
  sql_data:
    driver: local

So, let’s run the docker-compose file and check that everything is working correctly::

Now, we define a Sql script to create:

a new database named TestDB.
two tables: Employees and Departments, with some sample data.
a view v_EmployeeDetails that joins the two tables.
a function fn_GetDepartmentID that returns the DepartmentID for a given DepartmentName.
a sample stored procedure sp_GetEmployeeDetails to demonstrate our SearchInAll procedure’s capability to find procedures.

-- Create a new database
CREATE DATABASE TestDB;
GO

USE TestDB;
GO

-- Create a couple of tables
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(100)
);
GO

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);
GO

-- Insert sample data into tables
INSERT INTO Employees (EmployeeID, EmployeeName, Department) VALUES
(1, 'John Doe', 'Finance'),
(2, 'Jane Smith', 'IT'),
(3, 'Alice Johnson', 'HR');

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Finance'),
(2, 'IT'),
(3, 'HR');
GO

-- Create a view
CREATE VIEW v_EmployeeDetails AS
SELECT e.EmployeeName, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.Department = d.DepartmentName;
GO

-- Create a function
CREATE FUNCTION fn_GetDepartmentID (@DepartmentName VARCHAR(100))
RETURNS INT
AS
BEGIN
    RETURN (SELECT DepartmentID FROM Departments WHERE DepartmentName = @DepartmentName)
END;
GO

-- Create a procedure
CREATE PROCEDURE sp_GetEmployeeDetails
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID
END;
GO

Finally, we define another Sql script to create the SearchInAll Stored Procedure:

CREATE PROCEDURE [dbo].[SearchInAll]
    @strFind AS VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    -- Create a temporary table to store results
    CREATE TABLE #SearchResults (
        ObjectType VARCHAR(50),
        ObjectName VARCHAR(256),
        ObjectDefinition VARCHAR(MAX)
    );

    -- TO FIND STRING IN ALL PROCEDURES
    INSERT INTO #SearchResults (ObjectType, ObjectName, ObjectDefinition)
        SELECT 'Stored Procedure' AS ObjectType,
               OBJECT_NAME(OBJECT_ID) AS ObjectName,
               OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + @strFind + '%';

    -- TO FIND STRING IN ALL VIEWS
    INSERT INTO #SearchResults (ObjectType, ObjectName, ObjectDefinition)
        SELECT 'View' AS ObjectType,
               OBJECT_NAME(OBJECT_ID) AS ObjectName,
               OBJECT_DEFINITION(OBJECT_ID) AS ObjectDefinition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + @strFind + '%';

    -- TO FIND STRING IN ALL FUNCTIONS
    INSERT INTO #SearchResults (ObjectType, ObjectName, ObjectDefinition)
        SELECT 'Function' AS ObjectType,
               ROUTINE_NAME AS ObjectName,
               ROUTINE_DEFINITION AS ObjectDefinition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%' + @strFind + '%'
          AND  ROUTINE_TYPE = 'FUNCTION';

    -- TO FIND STRING IN ALL TABLES OF DATABASE
    INSERT INTO #SearchResults (ObjectType, ObjectName, ObjectDefinition)
        SELECT 'Table Column' AS ObjectType,
               t.name AS ObjectName,
               c.name AS ObjectDefinition
        FROM   sys.tables AS t
        INNER JOIN sys.columns AS c ON t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%' + @strFind + '%';

    -- Return the results
    SELECT * FROM #SearchResults;

    -- Clean up
    DROP TABLE #SearchResults;
END

Now, using a SQL client, we will run all the Sql scripts:

Then, we will run the script to create the SearchInAll Store Procedure:

Now, we will run same queries for every object, in order to check the data in the database:

Finally, using the SearchInAll Stored Procedure, we will search some strings in all database objects:

[search ’employee’]

[search ‘DepartmentID’]

[search ‘DepartmentName’]

[search a string that might not exist]

It is important to highlight that depending on how our SQL Server is configured, searches might be case-sensitive or case-insensitive.
In my case, the SQL Server is configured case-insensitive.

SQL Server – How to Search for Objects in a database

Leave a Reply Cancel reply