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!