How to find and safely rename SQL objects in SQL Server databases

Frequently, developers and DBAs need to find SQL objects in databases for which name you only partially remember. Searching manually these objects can be a time-consuming job, especially when a database has a large number of objects.

In a situation when a search for specific data is needed in all database tables, views, etc. you should run a SELECT statement several times depending on the number of database tables or views that want to search. For some searches, you need to write a complex Select statement query, which requires advanced SQL Server development skills.

Furthermore, as a part of some tasks, you get a database that is not documented properly or completely, and you need to look for specific stored procedures that reference a specific table or process. Once you find SQL objects, you need to rename them and in doing that should pay attention not to break referential integrity.

This article will show some examples that can help in the above-mentioned situations. In the first part of the article, a couple of system views that can be used for finding SQL objects and data will be introduced, and in the second part of the article, the third-party software solution called ApexSQL Fundamentals Toolkit for SQL Server will be shown.

ApexSQL Fundamentals Toolkit for SQL Server pack consists of the following tools:

• ApexSQL Analyze
• ApexSQL Complete
• ApexSQL Decrypt
• ApexSQL Propagate
• ApexSQL Refactor
• ApexSQL Script
• ApexSQL Search

Find SQL objects

The first system view that will be introduced for finding SQL objects is sys.objects:

SELECT *
FROM sys.objects

When running the above code, all user-defined and schema-scoped objects that are created within a database, including natively compiled scalar user-defined function will be retrieved:

This is somewhat helpful. Let’s narrow down the search scope by defining condition in a Where clause:

SELECT NAME AS ObjectName
	,SCHEMA_NAME(schema_id) AS SchemaName
	,type
	,type_desc
FROM sys.objects
WHERE is_ms_shipped = 0
	AND NAME LIKE '%Emp%'
ORDER BY NAME;

This looks much better, but let’s list only the stored procedure by adding an additional condition under the Where clause “AND type_desc LIKE ‘%PROCEDURE%”:

SELECT NAME AS ObjectName
	,SCHEMA_NAME(schema_id) AS SchemaName
	,type
	,type_desc
FROM sys.objects
WHERE is_ms_shipped = 0
	AND NAME LIKE '%Emp%'
	AND type_desc LIKE '%PROCEDURE%'
ORDER BY NAME;

Subsets of the objects can be viewed by using system views for a specific type of object, such as:

• sys.tables
• sys.views
• sys.procedures

SELECT SCHEMA_NAME(schema_id) AS SchemaName
	,name
	,type
	,type_desc
FROM sys.procedures
WHERE name LIKE '%Emp%';

Another way to find SQL objects in a database is by using the SQL Server Management Studio (SSMS) Object Explorer Details feature.

In Object Explorer, select a database and, from the View menu, choose the Object Explorer Details command:

in the Search box, type search criteria, and press enter. This will return all user-defined and system objects that match search criteria:

To find SQL objects, the sys.syscomments and sys.sql_modules views can be used, too. But bear in mind that the sys.syscomments view will be removed in a future version of Microsoft SQL Server.

Find a string in the definition of stored procedure or function

All the above-mentioned system views are for finding SQL objects, but what about a situation when a string that is located in the body of some of the stored procedures or functions needs to be found.

For that, the INFORMATION_SCHEMA.ROUTINES view can be used. This system view can return one row for each stored procedure and function that can be accessed by the current user in the current database.

If searching for a specific string (keyword) or block of text in the stored procedures or functions, for example, a requirement to find all presents of some table (e.g. BillOfMaterials table), in the stored procedures or functions for a specific database, run the code below:

SELECT ROUTINE_NAME,ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%BillOfMaterials%'
	AND ROUTINE_TYPE = 'PROCEDURE'

As can be seen, all stored procedures with a definition in which a specified string is present will be listed.

This system view can be used to find a specific parameter in stored procedures or functions:

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

The ROUTINE_DEFINITION will return the first 4000 characters of the definition text of the stored procedure or function if it is not encrypted. Otherwise, it returns NULL.

Find data

A common way to find data in a database is using a Where clause with the utilization of the Comparison or Logical operators in the Select statements.

In the example below all employees whose job title contains the word ‘Marketing’ are listed:

SELECT e.JobTitle
	,p.FirstName
	,p.MiddleName
	,p.LastName
FROM HumanResources.Employee e
    INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
WHERE e.JobTitle LIKE 'Marketing%';

More about this topic can be found on the Different methods to search SQL Server data page.

Find SQL objects using a third-party tool

ApexSQL Search, SSMS/VS add-in is a part of the ApexSQL Fundamentals Toolkit for SQL Server pack that provides solutions for situations mentioned at the beginning of the article.

To find SQL objects in one or multiple databases, the Object search feature can be used. From Object Explorer, select a SQL Server or desired database, on the ApexSQL Search menu, choose the Object search command:

On the Object search pane, in the Search text box, enter the search string:

From the Server drop-down list, choose a SQL Server on which the databases are located that want to be searched, and from the Database drop-down list, choose one or more databases:

Additionally, the search string can be adjusted by specifying whether to match (e.g. Object name, Object body, etc.) to the searched text:

Under the Object types drop-down list, select the type of the objects that want to be searched:

When all is set, click the Find button. The objects that match the searched string will be displayed in the result grid:

To find SQL specific string in the body (definition) of a stored procedure or function, for example, places where the name of the BillOfMaterials table appears do the following.

In the Search text box field, enter the name of the table, in our case, that will be BillOfMaterials. Specify server/databases where the search will be performed, under the Object type drop-down list, choose the object that wants to be searched, in our example that will be the stored procedures and user-defined functions, make sure that Object bodies check box is checked, and click the Find button:

In the results grid, all results of the BillOfMaterials table will be shown. Click on one of them and the stored procedure build definition will appear. Right-click, and, from the context menu, choose the Find command. In the text box, type the name of the table and press Enter. All the results of the word BillOfMaterials in the stored procedure definition will be found:

Rename SQL object

Once we find the SQL object of interest (e.g. BillOfMaterials table), the renaming process and reflection that changes to the objects that depend on the renamed object becomes a trivial job by using the Safe rename feature.

In Object Explorer, under database three, find an object that wants to be renamed, right-click and, from the context menu, choose the Safe rename command:

Another, more intuitive, way to initiate this feature is through the Object search results grid itself. In the Object search results grid, right-click on the column result and, from the context menu, choose the Safe rename command:

The Safe rename dialog will appear. In the New name dialog box, enter a new table name and click the Preview script button. In the Generated script tab, a complete SQL script of the renaming process will be shown:

If you want to modify the script before execution, click the Create script button. The generated script will be opened in a new query editor. Now, all is needed is to click the Execute button and the renaming process will be done.

Conclusion

Finding SQL objects and renaming them can be an easy job with the right tool. There is no need to analyze a whole database and search for dependencies yourself. With ApexSQL Fundamentals Toolkit for SQL Server pack and ApexSQL Search tool you can find all dependent objects, rename and drop–recreate when necessary, and avoid breaking database integrity.