Search by keyword in Stored procedure, Views, Functions And Tables

 CREATE PROCEDURE [util].[SearchAll]       

(@strFind AS VARCHAR(MAX))
AS
BEGIN
    SET NOCOUNT ON; 
    --TO FIND STRING IN ALL PROCEDURES        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) SP_Name
              ,OBJECT_DEFINITION(OBJECT_ID) SP_Definition
        FROM   sys.procedures
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL VIEWS        
    BEGIN
        SELECT OBJECT_NAME(OBJECT_ID) View_Name
              ,OBJECT_DEFINITION(OBJECT_ID) View_Definition
        FROM   sys.views
        WHERE  OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@strFind+'%'
    END 

    --TO FIND STRING IN ALL FUNCTION        
    BEGIN
        SELECT ROUTINE_NAME           Function_Name
              ,ROUTINE_DEFINITION     Function_definition
        FROM   INFORMATION_SCHEMA.ROUTINES
        WHERE  ROUTINE_DEFINITION LIKE '%'+@strFind+'%'
               AND ROUTINE_TYPE = 'FUNCTION'
        ORDER BY
               ROUTINE_NAME
    END

    --TO FIND STRING IN ALL TABLES OF DATABASE.    
    BEGIN
        SELECT t.name      AS Table_Name
              ,c.name      AS COLUMN_NAME
        FROM   sys.tables  AS t
               INNER JOIN sys.columns c
                    ON  t.OBJECT_ID = c.OBJECT_ID
        WHERE  c.name LIKE '%'+@strFind+'%'
        ORDER BY
               Table_Name
    END
END

exec [util].[SearchAll]  'username'

Find table and column name in MSSQL

It is time consuming job to browse each and every tables and columns to find required tables  and it's even difficult to search columns name in any database. There's a easy way to do the search.

For example, We want to search all the tables which contains user in the table names in sanjeebDB database:

select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 

where TABLE_NAME like '%user%' 

     ORDER by TABLE_NAME  

Next example, we want to search all the tables which contains 'username' a column name:

select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME  like '%username%'

If we need these functions repetitively then we can create a stored procedure and invoke the stored procedure passing keyword as a parameter:

 Create PROCEDURE [UTIL].[FIND_TABLE]
(
    @pTableName VARCHAR(max)
)
AS
BEGIN
     select * from sanjeebDB.INFORMATION_SCHEMA.TABLES 
     where TABLE_NAME like '%' + @pTableName + '%' 
     ORDER by TABLE_NAME  
END

create PROCEDURE [UTIL].[FIND_TABLE_BY_COLUMN]

(

    @pColumnName VARCHAR(max)

)

AS

BEGIN

     select * from sanjeebDB.INFORMATION_SCHEMA.COLUMNS where             COLUMN_NAME  like '%' + @pColumnName + '%'

END

To use them, run the below sql query:

exec util.FIND_TABLE 'user'

exec util.FIND_TABLE_BY_COLUMN 'username'

Good Luck!