SQL Server Graph - How to edit schema without loosing data

moondaddy 911 Reputation points
2022-03-16T01:58:01.28+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-03-16T05:37:31.027+00:00

    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.


  2. Erland Sommarskog 102.1K Reputation points MVP
    2022-03-16T23:07:19.263+00:00

    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.

    0 comments No comments