This article explores various ways to search for database objects in SQL database such as tables, stored procedures, functions, and views.
Introduction
SQL Server has many database objects such as table, view, stored procedure, function, constraints, rule, Synonym, triggers. You being a developer or database administrator might work with thousands of databases. Although you should use a proper naming convention for database objects, it is a difficult task to remember the object names. Let’s consider a single production instance that consists of many databases. You want to look for a specific database object but not sure it exists in which database. It is not possible to explore each database and view the object.
In this article, we explore various ways to search for database objects in the SQL database.
Use sys.objects system catalog view
We can use system catalog view sys.objects to view all objects in a SQL database. It has a column type that contains the object category. For example, if we want to search only for the user-defined table, we use ‘U’ value for the type column.
I filter records for the useful columns. You can get all columns using the select * statement.
|
1 2 3 4 5 6 7 8 9 |
USE [AdventureWorks]; GO SELECT name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, type_desc, create_date, modify_date FROM sys.objects WHERE type ='u' |
Similarly, we use the value ‘P’ for the stored procedure.
|
1 2 3 4 5 6 7 8 9 |
USE [AdventureWorks]; GO SELECT name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, type_desc, create_date, modify_date FROM sys.objects WHERE type ='p' |
You can also search for objects modified in ‘N’ number of days. In the below query, we try to find output objects changed in the last 60 days in the sample database [adventureWorks].
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [AdventureWorks]; GO SELECT name AS [Name], SCHEMA_NAME(schema_id) AS schema_name, type_desc, create_date, modify_date FROM sys.objects WHERE modify_date > GETDATE() - 60 ORDER BY modify_date; GO |
You can refer to Microsoft docs for different values for the Type column in the sys.objects.
Use System Information Schema views
We can also use information schema views to search for the specific SQL database objects. For example, we use information_schema.Tables to find out specific tables in the SQL database.
In the script below, we search for the [Demotable].
|
1 2 3 |
SELECT * FROM information_schema.Tables WHERE [Table_Name]='demotable' |
We can use this schema view to find database view as well if we run above query without a WHERE clause, it returns both SQL tables and views.
Similarly, we can use information_schema.CHECK_CONSTRAINTS to find out check constraints in the current database.
|
1 2 |
SELECT * FROM information_schema.CHECK_CONSTRAINTS |
We can use information_schema.SCHEMATA to search for schema and their owners using the below query.
|
1 2 3 4 |
SELECT catalog_name AS DBName, Schema_name, schema_owner FROM information_schema.SCHEMATA; |
You can check the complete list of information schemas using the Microsoft docs.
Search object using SSMS object explorer details
We can use SSMS in-built object search functionality to find out specific objects across all online databases in SQL instance.
Navigate to View-> Object Explorer Details in SSMS. You can use a keyboard shortcut F7 to open it.
It opens the following screen and shows the various folders – Databases, Security, Server objects, Replication, PolyBase, Always on High Availability. You can also see the search box, as highlighted below.
Search objects in a single SQL database
Here, you can either search the object in a specific database. For that, you can browse the database folder and select the required database.
Now, enter the object name in the search box and press enter.
It searches the object and gives you a result, as shown below. You also get the path in a PowerShell output format.
Search object in all online SQL databases
You can search for objects in all databases in the connected instance using this object explorer search. On the home page of the object explorer, enter the object name and search.
In the result below, you see that a specified object exists in multiple databases.
You can browse to the specified object in the database using the object explorer. Click on the particular object and navigate to Synchronize.
It takes you to the object, as shown below.
Filter objects in SQL databases using SQL Server Management Studio
Suppose you know the database in which the object exists. You can filter objects in SSMS to show only relevant objects to you. For example, let’s say we need to search only tables with [HumanResources] schema in the [AdventureWorks] database.
To filter the tables, expand [AdventureWorks] database, right-click on the tables and click on Filter settings.
It opens the following filter setting page. You have multiple filter options. You need to specify one or more filter conditions. I specify schema [HumanResources] as shown below:
Click Ok, and it filters the results as per the specified condition. You can use equal, not equal and contains an operator in the filter.
Once it applies filters, it changes the folder name as well from tables to tables (filtered). It helps you to recognize that you are viewing filtered objects in SSMS.
We can remove or modify the filter to go back to the original states in the SSMS object browser. Go back to tables (filtered) and select the options – Remove Filter to clear the filter or Filter Settings to view filter, change it, if required.
Use ApexSQL Search in SSMS to search for SQL database objects
ApexSQL gives you a tool ApexSQL Search to install in SSMS and Visual Studio for object search.
Download and install it in your system to integrate with SSMS or Visual Studio. Once installed, Launch SSMS, Connect to SQL instance and navigate to ApexSQL Search -> Object search.
It opens the object search windows.
In this window, you can do the following configurations:
- Search text: Enter the keyword you wish to search
- Server: It is the SQL instance you connected
- Database: Here, you can select a single database, multiple databases or all databases
- Object type: By default, it searches in all the objects. You can expand object types and select the specific objects if we want a specific search result. For example, if we want to search the object in the table and stored procedure, just select the required options
Let’s search for the objects containing employee keyword, and you get the detailed results.
Let’s search for specified object names in the stored procedures and user tables. It gives you the flexibility to view the object definition as well as search results.
You can perform another level of object filter using this ApexSQL Search. Suppose for employee keyword you get 100 results. Now, you want to filter those 100 results as per below:
- An object should belong to schema [HumanResources]
- Its name should contain a login word
To set these filters, click on the row below the column name, select the comparison operator and your condition.
It immediately filters the results, and you get an object as per your requirement.
Right-click on the object, Navigate to object explorer node.
It takes you to a specific object in the SSMS.
By default, it does not perform an exact search for the keyword you specified. In case, we want an exact search for the object name specified so we can put a check on – Exact match as shown below.
ApexSQL Search is an excellent tool to search for specific objects in all databases of SQL Server. It is integrated well with SSMS so you can easily use it without launching any external program.
Conclusion
In this article, we explored various ways to search for SQL database objects in SQL Server. We can use T-SQL, object explorer search, SSMS filter, as well as third-party tools such as ApexSQL Search for the same. You can choose the appropriate tool and search for objects.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023