SQL Server Graph - How to edit schema without loosing data

Question

I'm building a graph database in SQL Server and when I need to rename or modify a node or edge, I have to delete the objects and recreate them which also deletes the data.

What's the standard way of preserving the data in these situations?

I'm at the beginning of developing a large database and for sure there will be lots of changes as I go. I will also be loading a lot of test data that I don't want to loose.

Thank you.

Answer 1

Hi @moondaddy
You can change the schema of a database object by using the following script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName  

The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'  
FROM sys.Objects DbObjects  
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id  
WHERE SysSchemas.Name = 'OldSchemaName'  
AND (DbObjects.Type IN ('U', 'P', 'V'))  

For more details , please refer to this link: How to change schema of all tables, views and stored procedures in MSSQL
Note：Taking backups is utmost important.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

A common way is to create a new version of the table and copy data over. What I typically do is that I create a side schema and transfer the old table to that schema. (Li's post comes in handy, after all!) Then I create the new definition of the table in my regular schema.

You will need to take care of indexes, triggers, foreign keys, including referencing foreign keys.