SQL SERVER RENAME TABLE HOW TO

Now, in the script, we can change the name of the stored procedure and click on execute.

In the above example, we are renaming a stored procedure with the name “ GetRecordsByDept” to the name “ GetEmployeesByDept“.Ĭopy and rename a stored procedure in SQL Server using SSMS USE ĮXEC sp_rename 'GetRecordsByDept', 'GetEmployeesByDept' For this demonstration, consider the following example.

SQL SERVER RENAME TABLE HOW TO

Now, let’s understand how to rename the stored procedure using sp_rename. In the case of stored procedures, we don’t need to specify this argument. The third is the type of object that is being renamed.Here we need to specify the new name for the stored procedure. The second is the new name for the specified object.Here we need to specify the current procedure name that we want to change. The first is the current name of a user object.

The sp_rename accepts 3 input parameters, EXEC sp_rename 'object_name', 'new_name' Let’s take a look at the syntax of using sp_rename in SQL Server. The sp_rename is a system stored procedure that is used to rename the user-created objects in a database. Now, if the procedure exists then, the DROP PROCEDURE statement will be executed to delete that procedure.Īnd then, we have created a new procedure with a different name but its functionality is the same. WHERE Department = the above example, first, we have used the IF statement to check whether the procedure with the given name exists or not. IF OBJECT_ID ('GetEmployeesByDept') IS NOT NULL WHERE Department = we will drop this stored procedure and create the same stored procedure with a new name. SELECT Name, gender, Department FROM dbo.Employees The script for this procedure is as follows. This procedure takes the department name as an input parameter and returns all the records of that department. So, first, we created a simple stored procedure in the sqlserverguides database. Next, to determine the dependencies of the stored procedure, right-click it and click on “ View Dependencies“.Next, expand the Programmability directory and then, expand Stored Procedures.After this, expand the database in which the stored procedure was created.First, connect to the Database Engine using the credentials, and then, from the Object Explorer, expand the database instance.Steps to rename a stored procedure in SQL Server using SQL Server Management Studio are illustrated below. This usually occurs if the objects are not updated to reflect the changes.Īfter considering all these limitations, let’s understand how to rename the stored procedure. When the name of a procedure is changed, it might cause dependant objects to fail.To change that, we need to drop that stored procedure and recreate it with a new name. When a stored procedure is renamed, the object name in the definition column of the sys.sql modules view does not change.Also, all the assigned permissions to that stored procedure are removed. Whereas if we delete and recreate the stored procedure then, a new object_id is created. When we rename a stored procedure, the object_id and permissions associated with that procedure are retained.These rules clarify how we should name a stored procedure in SQL Server. The name of the stored procedure should follow the identifier rules.USE AdventureWorks2012 ĮXEC sp_rename 'Sales.But before renaming a stored procedure, let’s take a look at some of the limitations and restrictions of renaming a stored procedure in SQL Server. Copy and paste the following example into the query window and select Execute. The following example renames the SalesTerritory table to SalesTerr in the Sales schema. In Object Explorer, connect to an instance of Database Engine. To cancel this action, press the ESC key before leaving this field.įrom the File menu, choose Save table name. In the field for the Name value in the Properties window, type a new name for the table. In Object Explorer, right-click the table you want to rename and choose Design from the shortcut menu. Using SQL Server Management Studio To rename a table Use sys.sql_expression_dependencies to list dependencies on the table before renaming it. For example, if you rename a table and that table is referenced in a trigger, you must modify the trigger to reflect the new table name. You must manually modify any objects that reference the renamed table. Renaming a table doesn't automatically rename references to that table. If existing queries, views, user-defined functions, stored procedures, or programs refer to that table, the name modification makes these objects invalid.īefore You Begin Limitations and Restrictions Think carefully before you rename a table.

Sql server rename table