Deleting or changing objects may affect other database objects like views or procedures that depends on them and in certain instances, can “break” the depending object. An example can be that if a View queries a table and the name of that table changes. The View will no longer function.
June 9, 2016SQL Server database object search
Developers and DBAs often perform database searches in order to find and obtain additional information related to specific objects, sometimes without knowing the full name of the object.
April 11, 2016How to rename database schema and object names without breaking SQL database dependencies
The main purpose of renaming an object or a column in SQL Server is to increase readability, adopt naming conventions, or to give a more meaningful name to an object. The sp_rename procedure will not rename nor update all SQL database dependencies for a renamed object or a column automatically. Therefore, all object dependencies should be found by using e.g. the sys.sql_expression_dependencies function and renamed manually.
ApexSQL Search, a SQL Server Management Studio and Visual Studio add-in among other features, provides users the Safe rename feature – the ability to rename tables, procedures, views, functions and columns without breaking SQL database dependencies
Let us show the complete process of changing the name and schema of a table at the same time.
To use the Safe rename feature:
-
Right-click a table to be renamed in the SQL Server Management Studio’s Object Explorer
- Select the Safe rename command from the context menu:
This will open the Safe rename table window. The first thing that can be noticed is the yellow warning icon, which informs about the existence of a table with the same name under the specified schema. If the schema and table name are properly changed, the warning will disappear:
After choosing another available schema and specifying the new table name, with a click on the Preview button, the following Preview tabs will be populated with information:
- Generated script
- Warnings
- Sequence
- Dependencies
The Generated script tab
The preview tab displays a SQL script that will be executed in order to rename the selected table. It can be opened in a new query window by selecting the Open button, ready for editing or executing via SQL Server Management Studio:
The Warnings tab
Second in order, the Warnings tab, will provide warnings, for example, in case the rename process cannot be completed without a table rebuild. Such scenarios are very rare and complicated to demonstrate, but the Safe rename feature is aware of them and will raise a warning if this is the case.
The Sequence tab
This tab summarizes the generated SQL script in a tree-like form by showing actions in the order they will be performed. Please note that the action sequence is specifically generated in such a way as to prevent any dependency problems:
The Dependencies tab
Finally, the Dependencies tab, provides a list of dependent objects that refer to the table which will be renamed. These are handled via Alter statements in the generated SQL script by changing their references accordingly:
As it can be seen, the Safe rename feature provides a detailed preview in several categories of what’s affected and how an object rename process will be executed. These can be further analyzed, or simply and safely rename the object with a click on the Execute (F5) button.
March 26, 2015
How to visualize SQL database objects dependencies
ApexSQL Search is a SQL Server Management Studio and Visual Studio add-in that, among other features, provides object a database dependency diagram.
The View Dependencies feature has the ability to visualize all SQL database objects’ relationships, including those between encrypted and system objects, SQL server 2012 specific objects, and objects stored in databases encrypted with Transparent Data Encryption (TDE). Let’s take a closer look at this feature.
March 18, 2015How to quickly search for SQL database data and objects in SSMS
Frequently, developers and DBAs need to search databases for objects or data. If a database function that contains a specific table column or a variable name, or for a table that contains specific data is searched, the simple solution for finding those values, does not exists.
April 4, 2013How to change an object name without breaking your SQL database
Very often, initial database design does not fit the current criteria and requirements anymore. It is therefore necessary to periodically align to what our current needs and standards are. When working with a large database, any idea of changing the name of one or more SQL objects can be a real challenge.
Changing an object name is a simple operation and it could be done easily using the sp_rename system stored procedure or by utilizing the rename and alter … rename statements for indexes and triggers.
April 4, 2013How to rename a column without breaking your SQL database
Renaming a table or view column is not something a SQL developer needs to do every day. However, when the time comes, how can it be done easily and effortlessly?
April 4, 2013
