How to Change a Table Name Using an SQL Query

How to change a table name using an SQL query?

Use sp_rename:

EXEC sp_rename 'Stu_Table', 'Stu_Table_10'

You can find documentation on this procedure on MSDN.

If you need to include a schema name, this can only be included in the first parameter (that is, this cannot be used to move a table from one schema to another). So, for example, this is valid:

EXEC sp_rename 'myschema.Stu_Table', 'Stu_Table_10'

How to set table name in dynamic SQL query?

Table names cannot be supplied as parameters, so you'll have to construct the SQL string manually like this:

SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 

However, make sure that your application does not allow a user to directly enter the value of @TableName, as this would make your query susceptible to SQL injection. For one possible solution to this, see this answer.

How to rename a table in SQL Server?

To rename a table in SQL Server, use the sp_rename command:

exec sp_rename 'schema.old_table_name', 'new_table_name'

SQL - Can I rename a table name in a view?

One way may be to write a stored procedure with a dynamic ALTER VIEW statement in it which sets the table name according to the needed month name. Then set up a job to run the procedure at midnight on the first of the month.

Something along these lines (pretty much just pseudo code):

CREATE PROCEDURE dbo.AlterViewWithMonthlyTableNames
AS

BEGIN

  DECLARE @TableName SYSNAME = CONCAT(CAST(GETDATE() AS NCHAR(3)),'_Data')
         ,@SQL NVARCHAR(1000);

  SET @SQL = CONCAT
    (
    'ALTER VIEW dbo.YourView
      AS
      SELECT *
      FROM ', QUOTENAME(@TableName),';'
    );

  EXEC sp_executesql @SQL;

END

Creating a SQL Loop to Change the Table Name

Your QUOTENAME should be around the entire table name.

Declare @Query VARCHAR(MAX)
DECLARE @i INT
CREATE TABLE #Accounts (Account varchar(10))
SET @i = 1

WHILE @i <= 12
BEGIN

print quotename(@i)
Select @Query = 'Select COUNT(ACCT) From dbo.'+quotename('table_'+convert(nvarchar,@i))
   INSERT INTO #Accounts
   EXEC(@Query)
   SET @i = @i + 1;
END;

SELECT Account FROM #Accounts

SQL Server - Ways of renaming a table name

You can execute a stored procedure with or without the EXEC Keyword. So Both your approaches are correct and has the same effect.

All the below 3 approaches are valid but the most commonly used is the 1st one

EXEC sp_rename 'old table name','new table name';

EXECUTE sp_rename 'old table name','new table name';

sp_rename 'old table name','new table name';

---

Related Topics