A SQL Server database includes plenty of objects – tables, views, stored procedures, functions, triggers, etc. With the increasing complexity of modern databases, it becomes challenging to locate a specific object, especially when the database contains thousands of objects.
Even with proper naming conventions for all objects, it is impossible to remember all names. Developers and analysts require efficient methods to search for objects, data, or code fragments within the database. This article will examine the available methods for quickly locating objects in SQL Server databases. With the right approach, you can save valuable time and effort.
Contents
- Search with standard scripts
- Search in SSMS
- Search using the dbForge Search for SQL Server add-in
- Conclusion
Search for objects and text with standard scripts
Manual searching for database objects can be time-consuming and drain your energy and resources. Still, it is essential to locate objects in databases for tasks such as data analysis, troubleshooting, maintenance, performance optimization, and more.
Fortunately, SQL Server provides various methods for searching for objects and text within the database.
Search for database objects in sys.objects
The sys.objects view provides information about all objects in a database, including tables, views, stored procedures, functions, and more. It contains a row for each object with detailed information about the object, such as its name, type, description, unique ID, creation date, the ID of the schema containing that particular object, and more.
The simplest query that brings us the complete information of all database objects is as follows:
SELECT * FROM sys.objects;We can modify this query to fetch the most essential information about objects:
SELECT
Name AS [object_name]
,schema_name(schema_id) AS schema_name
,type
,type_desc
,create_date
,modify_date
FROM sys.objects
You can refine your search results by setting specific criteria. For instance, in the following example, we’ll retrieve all user-defined tables from the AdventureWorks2019 sample database:
SELECT
Name AS [object_name]
,schema_name(schema_id) AS schema_name
,type
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type = 'U';This query returns the data about all objects in the database where the type column is ‘U‘ (a user table). The results will also include more descriptive information for each object – its type, description, creation date, and the last modifying date.
By specifying various object types in the WHERE clause, you can generate a comprehensive list of matching objects within the database.
Additionally, you can also search for objects based on text content. For example, you can use the LIKE operator with the % wildcard characters to search for all objects whose names contain a specific text string. By combining this text-based search with the search based on the object type, you can achieve even more targeted results.
In our case, we need to find all user tables that contain the Address string in their names:
SELECT
Name AS [object_name]
,schema_name(schema_id) AS [schema_name]
,type
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE type = 'U'
AND name LIKE '%Address%';
The same principle applies to all other database objects such as procedures, functions, and others. You can locate them by specifying the appropriate object type and providing the search key text string.
For SQL Server specialists, using the sys.objects view is an efficient and convenient option to locate specific objects within their databases. However, it is not the only approach available.
Search for data in information_schema
The information_schema view provides developers with the ability to search for database objects. It is essentially a virtual database that holds metadata about all objects within the database, including tables, columns, views, indexes, and more.
SQL Server developers can leverage the information_schema view to gather information about the database structure, find specific objects, and understand their properties and relationships. This can be particularly useful when working with complex SQL queries or unfamiliar databases, as it eliminates the need to individually explore each aspect of the database.
To search for a specific table using the information_schema view, the following query can be used:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE [TABLE_NAME] = 'Address';
It is also possible to query the list of all tables contained by some definite schema:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'Person';This query brings us the list of all tables in the Person schema:
It can be challenging to locate a specific procedure in a database containing numerous stored procedures. Fortunately, the information_schema offers a solution through its ROUTINES view, which provides information on all stored procedures. By querying it, we can effectively search for a procedure by text.
SELECT
ROUTINE_CATALOG
,ROUTINE_SCHEMA
,ROUTINE_NAME
,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'uspGetBillOfMaterials'
AND ROUTINE_DEFINITION LIKE '%ProductAssemblyID%'This query returns the information about the procedure containing the necessary text in the ROUTINE_DEFINITION field:
To search for the definite text string across all procedures, use the LIKE operator with wildcards:
SELECT
ROUTINE_CATALOG
,ROUTINE_SCHEMA
,ROUTINE_NAME
,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%ProductAssemblyID%';
By querying the sys.objects and information_schema system views, SQL Server developers can locate the database objects and obtain the related information without inspecting the tables manually. However, this method can be time-consuming and cumbersome compared to using a graphical user interface (GUI) tool for searching the objects.
Search for database objects in SSMS
SQL Server Management Studio (SSMS) is the default GUI tool for managing and administering SQL Server databases. It is highly functional and user-friendly. With regard to search functionality, SSMS offers an in-built search option in the Object Explorer, enabling users to locate various database objects.
To search for a database object using the SSMS GUI, open this tool and connect to the database you want to search. Navigate to View > Object Explorer Details (or just click F7) to access the search area:
Type the name of the object you are searching for (e.g., table name, stored procedure name, etc.) and press Enter to initiate the search. SSMS will display the results.
Note that you can search for objects in all databases simultaneously. To achieve it, just enter the search key term and don’t select the specific database:
When you have located the target object in the result, you can access it quickly – right-click on the object and select Synchronize. This will transfer you to that object directly to continue with any required tasks on it.
However, using SSMS to search for database objects has its limitations. The basic text-based search function offered by this solution may not be suitable for complex search requirements, and the search speed can become slow when dealing with databases that contain a large number of objects.
Fortunately, SSMS can be enhanced with third-party tools that make up for these shortcomings and improve its functionality, allowing users to work more efficiently in a familiar environment. If you are looking for a faster and more accurate search solution, the dbForge Search add-on for SSMS is a great option to consider.
Search for database objects and text using dbForge Search for SQL Server
dbForge Search for SQL Server is a solution that aims to help database administrators and developers quickly locate any necessary database object.
It is a free add-on that integrates with SSMS – you are getting a separate Search tab – and improves its interface and provides advanced search options, such as:
- Define the search mode – look for DDL (objects) or data.
- Search for the specific substring.
- Search for the particular object types.
- Search in one of the multiple databases.
- Apply the case sensitivity, search for the exact match for a word, or search for string entries.
This way, you can configure a complex search process within a couple of clicks, visually, instead of writing a sophisticated query.
To start searching for database objects, follow these steps:
Navigate to the dbForge Search tab and select the database from the menu (here you can choose one or multiple databases). Enter what you want to find into the Search field and specify additional parameters (object name, type, etc.) and search options (case sensitivity, wildcards, etc.) if needed. Click Search.
The results will be present in the below tab with all the details for each object and the option to navigate to its definition in the database. For better results’ readability, you can sort them, group them, and filter them.
To search for a specific string, switch to the Data search mode:
Using dbForge Search for SQL Server as an add-in to SSMS offers lots of advantages in comparison to using the SSMS Object Details feature only:
- Faster performance. dbForge Search scans even large databases quickly, overplaying other search methods and tools and saving time.
- Precise results. This add-on allows you to specify complex and detailed search criteria to apply them at once and bring you the target object or piece of data in one go.
- User-friendly interface. The integrated add-on improves the SSMS interface providing a simpler, more intuitive, and more accurate interface even for those with limited database knowledge.
- More productivity. Finding the necessary object faster means that database admins and developers can work on it at once, thus completing their tasks faster.
DbForge Search for SQL Server is a powerful tool that enhances the capabilities of SSMS, making it easier for SQL Server specialists to get their work done. This tool is free and accessible to everyone, providing a convenient and efficient way to search through databases.
Conclusion
Locating different database objects is a crucial yet repetitive task for every database developer or analyst. Therefore, it is the task that must be simplified and automated. Although database professionals are familiar with standard scripts, GUI tools can significantly speed up and simplify the job.
SQL Server Management Studio (SSMS) is an IDE favored by many SQL Server specialists. It offers decent search capacities to quickly locate the necessary database objects. However, these capabilities can be improved, and that’s where dbForge Search for SQL Server comes in.
dbForge Search acts as an add-on for SSMS, providing a more efficient way of searching for database objects and text. Additionally, it is part of the SQL Tools package – a comprehensive collection of enhancements for SSMS that enhance the functionality of the IDE and improve overall performance. The free trial of SQL Tools is provided for 30 days allowing you to evaluate all the advanced options properly.
