Search tables and procedures by given text in it

Tweet

Find all tables and procedures by giving string in it using like operator in SQL

1. FOR TABLES

    SELECT COLUMN_NAME, TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%searchstring%'
   

2. FOR PROCEDURES

    SELECT OBJECT_NAME(object_id), 
           OBJECT_DEFINITION(object_id)
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'