We will discuss this in more detail in the following examples. Most of the other major RDBMs allow you to rename a table with the ALTER TABLE statement, but this isn’t the case with SQL Server. The WHERE clause specifies which record (s) that should be updated.
In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table. Rename a Table in SQL Server (T-SQL) In SQL Server, you can use the sprename stored procedure to rename an object, including a table. WHERE condition Note: Be careful when updating records in a table Notice the WHERE clause in the UPDATE statement. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. For example, you should investigate which applications are using the table. In terms of security, any existing privileges that we granted to the old table must be manually migrated to the new table.īefore renaming a table, you should evaluate the impact thoroughly. Just like with naming tables, avoid using abbreviations, acronyms or special characters. When writing a query against the table, you should be prefixing the field name with the table name or an alias anyway. Note that you cannot use the RENAME TABLE statement to rename a temporary table, but you can use the ALTER TABLE statement to rename a temporary table. When naming your columns, keep in mind that they are members of the table, so they do not need the any mention of the table name in the name. sprename Employee, emp Note: Make sure that the original table name is correct, and the new name has not been used with other database objects otherwise, it will raise an error. The issue was, some tables were imported from some sources but their names were not as per the database standards. Use the sprename built-in stored procedure to rename a table in the SQL Server database.
In addition to the tables, we can use the RENAME TABLE statement to rename views.īefore we execute the RENAME TABLE statement, we must ensure that there is no active transactions or locked tables. Recently, I came across with a nice question in a forum, how to mass/bulk rename sql tables. If the new table new_table_name does exist, the statement will fail. The old table ( old_table_name) must exist, and the new table ( new_table_name) must not. RENAME TABLE old_table_name TO new_table_name Code language: SQL (Structured Query Language) ( sql )