How to Search SQL Object in Complete Database

This article we’ll learn how to search SQL object in complete database. You can search for database objects in SQL database such as tables, stored procedures, functions, and views.

In our day-to-day routine work some time we need to search object in SQL DB without any external software or add on etc. In below section we’ll explain you the ways using which you can search the required object in your DB.

      We’ll search using information_schema view. It provides various options like  

• How one can search table.
• How one can search any column in complete DB.
• How one can search procedure/ function name.
• How one can search any text within procedure or function in complete DB.

Search table using “information_schema” view:

Select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME
 like '%Pass your TABLE name here for search%'

Search procedures using “information_schema” view:

Select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME 
like '%Pass your procedure name here for search%'

Search any column using “information_schema” view:

Select * from INFORMATION_SCHEMA.COLUMNS  where COLUMN_NAME 
like '%Pass your COLUMN name here for search%'

Above queries will give result like below:

Let’s check out some other ways to do the above

How to Search a table in DB:

Below query will return all tables present in selected DB. 

SELECT name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='u'

If you want to search specific table then you can alter above query like given below, “and Name like ‘%Jobs%’” is added at the end of above query so that it can give only required table information. I searched for “jobs” table you can modify this as per your needs.  

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='u'
and Name like '%Jobs%'

How to Search a Stored Procedure/Functions in DB:

Below query will return all stored procedures/ functions present in selected DB. If you have noted that current query is same as above, only difference is with param TYPE in where clauses  ’U’ means “user defined tables” and “P” is for “stored procedures”. “FN” for functions. You can modify above query as per your need.   

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='P'

We can search the required procedure by filtering above query like below. I searched for “Job “and it will display list all procedures having Job in its name. 

SELECT 
name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, 
type_desc, create_date, modify_date
FROM sys.objects WHERE type ='P' and Name like '%Job%'

I hope with above explanation you have got the clarity of searching the object within your SQL DB without using any external tool. For more info you can check Microsoft QnA section.

For more knowledge of MS SQL, you can check this section HERE

You may also like:

• SQL server Column Encryption using symmetric key.
• How to Update using Select statement in SQL Server.
• SQL Server Interview Questions and Answers.
• Import One Database to another in SQL server.
• Generate database scripts in SQL server.
• Different ways to search SQL objects in database.
• SQL CASE statement
• SQL IIF function
• QUOTENAME function in SQL
• How to Take SQL Server Database Offline/Online
• How to use SQL ORDER BY in Your Queries
• SQL Paging using OFFSET and FETCH
• Fill Factor in SQL Server
• How to Search SQL Object in Complete Database
• How to generate SQL table script with data