-
Title
How to find and safely rename SQL objects in SQL Server databases -
Description
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:
-
Resolution
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.