My employer has the habit of giving customer specific tables a name, starting with the name of the customer. This makes it difficult to call the sp_rename stored procedure for renaming column names, as you can see:

/*Version 1:*/
sp_rename 'Customer.FinishedProduct.ShortDescr', 'ShortDescription', 'COLUMN';
sp_rename 'Customer.LogTrackAndTrace.StorePreceddure', 'StoredProcedure', 'COLUMN';
/*Version 2:*/
sp_rename '[Customer.FinishedProduct].[ShortDescr]', '[ShortDescription]', 'COLUMN';
sp_rename '[Customer.LogTrackAndTrace].[StorePreceddure]', '[StoredProcedure]', 'COLUMN';

Both versions don't work. Do you know how this can be written? (The name of a table is 'Customer.FinishedProduct'.)

I have 4 mysql databases that I'm replicating from a master to a slave. In the past, I have renamed one of them because the slave is also a dev server where it has a dev version of that schema with the same name as prod, to make things easier for devs for various reasons. Replication currently goes like this:

db1 => db1_prod
db2 => db2
db3 => db3
db4 => db4

(mind you, these aren't the actual names). This has worked for us in the past because db1 is actually a very unique name & we took care of the renaming by piping the dump through sed, such as:

mysqldump --master-data --databases db1 db2 db3 db4 | sed 's/db1/db1_prod/g;' | pv | pbzip2 -p2 > mysqldump.sql.bz2

Then on the slave we have the rename directives in my.cnf:

replicate-rewrite-db='db1->db1_prod'

This has always worked due to db1's actual unique name. However, my testing has shown that it will not work for the other DB names, because some of the strings actually exist in tables or rows. What we want to do is rename them all to _prod:

db1 => db1_prod
db2 => db2_prod
db3 => db3_prod
db4 => db4_prod

I've also tried:

s/db1/db1_prod/g

But when I grep'd the resulting file, it still got more hits than I expected, though I can't say exactly what because the output is a bunch of very long rows, but I know it's more than just the DB name. Best thing I can think of right now is to dump one at a time without the --databases flag in mysqldump, then specify manually which DB schema to restore it to, but I'm not sure how to do that while keeping replication running if I'm having to reset the binlog position on the slave for each DB restore. I did search around a bit & saw this post: https://dba.stackexchange.com/questions/8869/restore-mysql-database-with-different-name , but my use case is a little different since I have multiple DBs to consider. Is there a better/easier way to do what I'm trying to do? *Edit*: I guess I could open the dump file up & edit the 3 lines for each DB each time I need to restore:

-- Current Database: db1
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db1 /*!40100 DEFAULT CHARACTER SET latin1 */;

USE db1;

and edit those 3 lines for each DB.

I need to know how I can rename my hosts belonging to my replica environment in mongodb, I currently have the following configuration: rs.conf() { "_id" : "myreplicaset01", "version" : 3, "term" : 22, "protocolVersion" : NumberLong(1), "writeConcernMajorityJournalDefault" : true, "members" : [ { "_id" : 0, "host" : "mongodb01.lab.lo:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 }, { "_id" : 1, "host" : "mongodb02.lab.lo:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 }, { "_id" : 2, "host" : "mongodb03.lab.lo:27017", "arbiterOnly" : false, "buildIndexes" : true, "hidden" : false, "priority" : 1, "tags" : { }, "slaveDelay" : NumberLong(0), "votes" : 1 } ], "settings" : { "chainingAllowed" : true, "heartbeatIntervalMillis" : 2000, "heartbeatTimeoutSecs" : 10, "electionTimeoutMillis" : 10000, "catchUpTimeoutMillis" : -1, "catchUpTakeoverDelayMillis" : 30000, "getLastErrorModes" : { }, "getLastErrorDefaults" : { "w" : 1, "wtimeout" : 0 }, "replicaSetId" : ObjectId("64dc2d0cd950391d47b7972e") } } I would need to change the hosts for example: - "host" : "mongo1:27017" - "host" : "mongo2:27017" - "host" : "mongo3:27017" Thanks in advance for the help.

when I need to rename a table and consequently its primary key (and all other constraints) as well I use the following simple example script: if object_id('dbo.Radhe1',N'U') is not null drop table dbo.radhe1 create table dbo.Radhe1 ( id int identity(-1008,-1) not null, name nvarchar(50) null , constraint pk_Radhe1 primary key clustered (Id) ); insert into dbo.Radhe1(name)values('Krishna') go 1008 select count(*) from dbo.radhe1 exec sp_rename N'dbo.Radhe1.pk_Radhe1', N'pk__Radhe_to_be_removed'; exec sp_rename N'dbo.Radhe1', N'Radhe1_to_be_removed' select count(*) from dbo.Radhe1_to_be_removed what I have seen is that when I table is over a million rows or any substantial size, or the server is a busy one, people tend to do is rename the table, then drop and re-create the primary key, which I understand to be a lot of work, very intense on resources. for example (names unrelated to the previous example): /* rename existing tables */ EXEC sp_rename 'dbo.DocumentDetailSearchTags', 'dbo.DocumentDetailSearchTags_TOBEDROPPED' EXEC sp_rename 'dbo.SearchTags', 'dbo.SearchTags_TOBEDROPPED' /* update pk constraint names */ ALTER TABLE dbo.DocumentDetailSearchTags_TOBEDROPPED DROP CONSTRAINT PK_DocumentDetailSearchTags ALTER TABLE dbo.DocumentDetailSearchTags_TOBEDROPPED ADD CONSTRAINT PK_DocumentDetailSearchTags_TOBEDROPPED PRIMARY KEY CLUSTERED ( [documentDetailId] ASC, [searchTagId] ASC ) Now I don't know what would the sp_rename be doing behind the scenes and I havent had the chance to test this on a busy live server. the question is: on a busy live server, sp_rename to rename the primary key of a table would work better than alter table drop and add constraint ?

I have an older copy of a database. I retrieved it from customer system by taking a backup and restore it on my local PC. I would like to have a look at a more recent copy of that same database. Obviously I cannot do that, working on the same database, because my older copy would be overwritten, so I have first renamed my database from "Firm_Customer" to "Firm_Customer_orig". Now I would like to restore the more recent copy of the database, but this failed as explained in following error message: >System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS01\MSSQL\DATA\Firm_Customer.mdf' cannot be overwritten. It is being used by database 'Firm_Customer_orig'. (Microsoft.SqlServer.SmoExtended) I was expecting the DB rename from "Firm_Customer" to "Firm_Customer_orig" also to cause the renaming of the file 'C:\Program Files\Microsoft SQL Server\...\Firm_Customer.mdf', but apparently the DB rename is just "partial". Is there a way to enforce the DB rename to perform a FULL rename (everything, related to "Firm_Customer" MUST be renamed to "Firm_Customer_orig")? Oh, before I forget: I'm working with Microsoft SQL Server Management Studio, version "v18.12". Thanks in advance

So far I've seen ALTER TABLE RENAME, ALTER TABLE CHANGE and ALTER TABLE MODIFY. What I'm trying to achieve is something like ALTER TABLE my_table RENAME col_old_name TO col_new_name, which can be a case of modifying a column. [SQLite](https://www.sqlite.org/lang_altertable.html) goes with RENAME. [MariaDB](https://mariadb.com/kb/en/alter-table/) and [MySQL](https://dev.mysql.com/doc/refman/8.0/en/alter-table.html) have all three. [Postgres](https://www.postgresql.org/docs/current/ddl-alter.html#id-1.5.4.8.11) goes with RENAME. [Oracle](https://docs.oracle.com/database/121/SQLRF/statements_3001.htm#i2103924) has RENAME and MODIFY. Standards are unkown to [SQL Server](https://learn.microsoft.com/en-us/sql/relational-databases/tables/rename-columns-database-engine?view=sql-server-ver15) . What is the database-agnostic way for renaming (or generally altering) a column? Maybe I'm erroneously expecting the standard to cover DDL operations as well? I have code that works with multiple databases so would rather make the SQL part as portable as possible.

Currently, while creating tables, we create 4 to 5 extra columns with temp names like c0, c1, c2 etc. to be used later when needed and are left NULL by default. This way we don't have to create additional columns when needed. We just rename one of the temp columns created in the first place. I am wondering if this approach is good or not. Also, what is the performance difference if new columns are created vs renaming old columns.

I understand that renaming a table within the same database will not generally result in a table copy... It's not clear to me if this holds true for renaming across databases. For reference, I want to move/rename a ~1TB table to a different database, and I don't want to initiate a huge copy operation. Will renaming a MySQL table to another database result in a full table copy operation on disk? Please provide an explanation or references if possible.

INPUT: CREATE TABLE dist -> SELECT ST_DISTANCE(POINT(x1,y1),POINT(x2,y2)) -> FROM config; OUTPUT: +------------------------------------------+ | ST_DISTANCE(POINT(x1,y1) , POINT(x2,y2)) | +------------------------------------------+ | 140.0071426749364 | | 139.30183056945089 | | 138.6001443000692 | | 137.90213921473443 | | 137.20787149431334 | +------------------------------------------+ RENAME COLUMN INPUT > ALTER TABLE dist -> RENAME COLUMN ST_DISTANCE(POINT(x1,y1),POINT(x2,y2)) TO Values; > ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(POINT(x1,y1),POINT(x2,y2)) TO Values' at line 2

I need to change the name for a few databases, however the databases are used by a lot of applications managed by several different teams. It would take some time for all the applications to finish changing connections and database name references. Is there a way to allow the change to be done seamlessly? Was looking at synonyms to temporarily allow references to the renamed database but unfortunately it is only for database level.

We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for instance). We also used to have this thing concept in the code at some point, but then it was decided for various reasons to rename it (let's say to stuff), which was done over time. Now, the code no longer mentions thing except in SQL queries. We unfortunately can't easily rename the table or columns since they're being heavily used, and we were wondering how to deal with new tables referring to this concept of thing/stuff. * Some of the team think that the schema should be the most up-to-date as possible, and that we should use the new naming (stuff) everywhere new (in join table names and FK names referring to thing) * Others think that the database schema should always stay consistent, and thus we should keep using the old naming (thing) anywhere, and handle the renaming in the code itself We're trying to understand what are best practices here and what is advised to do in this situation where the DB schema and the code naming diverge?

We want to rename some sequences in our production database for [certain reasons](https://stackoverflow.com/a/38666275/6962) . Is there a way to do this safely in production without having to first close all connections to the database? It's fine if we get gaps in the ID sequence (e.g. it jumps from ID 123 to ID 200 or whatever) but we obviously don't want long locks causing delays/errors, don't want any risk of duplicate IDs and similar. We're on PostgreSQL 9.5.21 on Heroku. We've looked at [the docs](https://www.postgresql.org/docs/9.5/sql-altersequence.html) but are still uncertain about the consequences of running ALTER SEQUENCE old_id_seq RENAME TO new_id_seq; ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq'); in production. I guess one risk would be if stuff happens in mytable between those two commands. But what if we did something like this: -- Starting at a much higher value than the currently highest ID. CREATE SEQUENCE new_id_seq START 200; ALTER TABLE mytable ALTER COLUMN id SET DEFAULT nextval('new_id_seq'); DROP SEQUENCE old_id_seq; What are the risks of doing it that way?

After a while one may wish to do some light refactoring, such as giving more appropriate names to stored procedures as their functionalities are better established. For the life of me, I simply cannot rename anything in SQL in a multi-branch TFS environment without it being an onerous, manual process. Without using some third-party tool, how are you supposed to do it? P.S. This is just for renaming stuff in a way that doesn't require deployment scripting to migrate anything. That is its own giant can of worms . ---- When I try to rename a SQL entity (e.g. a table, stored procedure), I use the Visual Studio Find and Replace in Files utility and then rename the file, since the super-useful refactor tools that I use all day for C# do not exist for SQL. After enabling the advanced SSDT Publish Option to drop objects in target but not in source and *manually* verifying everything works (since the validations done by the build and the publish are severely lacking ) and convincing myself my rename was implemented correctly, I now have to merge changes to a couple other branches in TFS. Apparently TFS Power Tools Migrate of rename is bugged (but since Microsoft retired Connect, I can't follow up on that post) because all my renames are now showing as ADD changes. After undoing all those erroneous pending changes, the files are leftover on disk, so manually renaming the files throws file already existing errors. Upon deleting those orphaned files, I am back where I started with no automated tools to help, having to repeat all the changes in every target branch and having to manually *detect* then merge conflicts. To add insult to injury, my %AppData%\Local\Microsoft\VisualStudio\14.0\ComponentModelCache folder keeps getting corrupted for some unknown reason (which persists through a complete repair reinstall) such that renaming files only under *.sqlproj throws a null reference error at me about a parameter name 'o' upon which the file is renamed on disk but not in the solution which then corrupts my TFS workspace. ---- I don't know what is wrong with me, but it seems that renaming SQL entities is unofficially blocked by Microsoft in every possible devious way. P.S. There is no "rename" tag to put on this post, further suggesting that renaming stuff is not something people ever do in databases.

We've built a new Windows 2016 VM with SQL Server 2016 that we'll migrate a mission critical database currently on SQL Server 2008 R2 to. The original plan was to have users test, test, test and test each data feed coming in. There is pushback from our integration team on repointing the feeds because no one knows where they all are and of course never documented anything. The suggestion for the production cutover is to take the old Windows machine offline, rename the new Win server to the old Win servername , rename the SQL Server and hope everything works. This idea of course frightens me and my position is I've never even heard of this being done and it's a great documentation opportunity. Other than we'd be going into production with all the feeds untested, are there other caveats to doing this if I'm overruled?

I need to safely rename a table and be sure, that all usages of this table in functions are also renamed. Example:

CREATE TABLE test (
    id serial,
    name varchar(255)
);

CREATE FUNCTION public.last_test() RETURNS SETOF test
    LANGUAGE sql STABLE
AS $$
    SELECT
      *
    FROM
      test
    LIMIT 10;
$$;

ALTER TABLE test RENAME TO tests;

Now, when I look into function last_test, I see, that it still references old test table name. Is it even possible to rename a table and all it's usages in functions automatically?