SQL search for SQL Server objects

In this article, we will explain different ways of methods for finding SQL objects in SQL Server databases.

Transcript:

Hello, and welcome to this video presentation in which we will introduce different ways of SQL search methods for finding SQL objects in the SQL Server databases.

SQL search for objects

To find objects in a database, several methods can be used, and these methods include querying system objects.

INFORMATION_SCHEMA.ROUTINES view

Let’s start with the ROUTINES system information schema view. Using this view, you can search all procedures and functions in a database.

To find all procedures in a database, execute the following code:

SELECT ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';

The query returns the owner, name, and definition of stored procedures in the current database.

The same SQL search can be performed for functions too. To do this, instead of ‘PROCEDURE‘ in the WHERE clause, type ‘FUNCTION‘ and execute again:

SELECT ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION';

This time, the query returns functions in the current database.

The information schema routines view can be also used to find a specific parameter in all stored procedures. To do this, execute the following code:

SELECT ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%'
  AND ROUTINE_TYPE = 'PROCEDURE';

The query returns stored procedures with the StartProductID parameter in the current database.

sys.syscomments view

Another way to find a specific parameter under all procedures or functions is to use the system.syscomments view.

Execute this code, to find a specific parameter in all stored procedures:

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE TEXT LIKE '%@StartProductID%'
  AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id);

Note that this view returns only the name of stored procedures that contains the parameter.

Similar SQL search code can be executed to find the specific parameter for functions:

SELECT OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE TEXT LIKE '%@ProductID%'
  AND OBJECTPROPERTY(id, 'IsScalarFunction') = 1
GROUP BY OBJECT_NAME(id);

Besides finding information in stored procedure and functions, this view contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint.

The downside of this view is the fact that it will be removed in a future version of Microsoft SQL Server.

Therefore, instead of using the syscomments view, another view called sql_modules can be used for SQL search to achieve the same results.

sys.sql_modules view

Moving on, let’s have a look at another view called sql_modules. When executed, this SQL search code will return the same result as in the previous one:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
  AND DEFINITION LIKE '%@StartProductID%';

sys.objects view

One more useful view that can be used for searching SQL objects is the sys.objects view. Executing this code will find all user-defined procedures in a database:

SELECT name AS function_name
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
FROM sys.objects
WHERE type_desc LIKE '%PROCEDURE%';

Bear in mind that in SQL Server there are other solutions that can be used to find a specific object in a database. But for inexperienced developers and for those which prefer quick and fast results, let’s take a look at a third-party solution.

ApexSQL Search

ApexSQL Search is a SQL search add-in that can be integrated into SQL Server Management Studio and Visual Studio. With this tool, SQL objects in a database can be searched without typing and executing any code.

To find a specific object in a SQL Server database, from Management Studio or Visual Studio main menu, find ApexSQL menu, click ApexSQL Search and from the list, choose the Object search command:

The Object search pane will be opened. Here, in the Search text box, type the text that wants to be searched. In the Server and Database drop-down boxes, choose SQL Server and databases that you want to search in. Also, specified whether to search, in object body, name, system objects, and so on.

Additionally, under the Objects drop-down box, choose which objects type will be searched.

When everything is set, click the Find button. The results will be shown in the object search grid:

To find specified SQL search object in Object Explorer pane, right-click on the object in the result grid and from the context menu, choose the Navigate to object explorer node command:

Thanks for watching. For more information, please visit apexsql.com