SQL SERVER – Query to Search Anything Inside Stored Procedures From a DataBase

Search Anything Inside Stored Procedures

 -- =========================

 -- SEARCH INSIDE PROCEDURES

 -- =========================

 SELECT DISTINCT

    A.NAME

 FROM SYS.OBJECTS A, SYSCOMMENTS B

 WHERE

 A.OBJECT_ID = B.ID

 AND [TEXT] LIKE '%InputText%'

Search Stored Procedures Only From Specific Table(s)

We can filter further by using table, that store procedure depends.

 -- ==================================================

 -- SEARCH STORED PROCEDURES FROM SPECIFIC TABLE ONLY

 -- ==================================================

 SELECT DISTINCT A.NAME

 FROM SYS.OBJECTS A, SYSCOMMENTS B

 WHERE

 [TEXT] LIKE '%InputText%'

 AND A.OBJECT_ID = B.ID

 AND A.OBJECT_ID

 IN

 (

    SELECT DISTINCT OBJECT_ID 

    FROM SYS.SQL_DEPENDENCIES 

    WHERE referenced_major_id IN (object_id('TableName'))

 )

SQL SERVER – Query to Search Columns From a Database

Find Columns Inside a DataBase

The following query is used to find columns from all the tables of a database.

 -- ======================

 -- SEARCH TABLE COLUMNS

 -- ======================

 SELECT 

    TABLE_CATALOG,

    TABLE_NAME,

    COLUMN_NAME,

    ORDINAL_POSITION,

    IS_NULLABLE,

    DATA_TYPE,

    CHARACTER_MAXIMUM_LENGTH,

    NUMERIC_PRECISION,

    NUMERIC_SCALE

 FROM INFORMATION_SCHEMA.COLUMNS

 WHERE COLUMN_NAME LIKE '%ColumnName%'

 ORDER BY TABLE_NAME