{"id":7371,"date":"2026-07-28T06:08:29","date_gmt":"2026-07-28T13:08:29","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7371"},"modified":"2026-07-27T14:16:47","modified_gmt":"2026-07-27T21:16:47","slug":"refactor-your-database-with-sql-projects-in-vs-code","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/refactor-your-database-with-sql-projects-in-vs-code\/","title":{"rendered":"Refactor your database with SQL projects in VS Code"},"content":{"rendered":"

SQL database projects are a fundamental tool for keeping your database in source control, tracking changes and collaborating on quickly evolving database needs. Not to be forgotten, their superpower is being able to dynamically generate deployment scripts that match the declared state and the exact environment you’re deploying to. SQL project development becomes more challenging when a database needs serious changes, where the requirements of the system have evolved to the point of having to rename tables and move objects between schemas.<\/span><\/p>\n

\"Refactoring<\/a>
Learn more about SQL projects at aka.ms\/sqlprojects<\/figcaption><\/figure><\/p>\n

In plain T-SQL, we can rename a table or column without having to copy or move the data through the <\/span>sp_rename<\/code><\/span> and modify the schema of an object with <\/span>ALTER SCHEMA ... TRANSFER<\/code><\/span>. In this post we’ll walk through an example of using SQL projects in VS Code to automatically generate these steps as part of the deployment plan. As a result, we’ll be able to seamlessly handle significant refactors of our database across multiple environments and continue to capture ongoing development in source control.<\/span><\/p>\n

The SQL Database Projects extension for Visual Studio Code supports refactoring database objects while preserving the intent of the change through deployment. When you rename an object or move it between schemas, SQL projects update the source files and record the operation in a refactor log so deployments can use <\/span>sp_rename<\/code><\/span> or <\/span>ALTER SCHEMA ... TRANSFER<\/code><\/span> rather than recreating the object. <\/span><\/p>\n

Working on the AdventureWorks sample database, the changes we will make are:<\/span><\/strong><\/p>\n

    \n
  1. add a new schema <\/span>[ProductCatalog]<\/code><\/span><\/li>\n
  2. move product description to ProductCatalog<\/span><\/li>\n
  3. rename SalesOrderDetail to SalesOrderLine<\/span><\/li>\n<\/ol>\n

    Create a SQL project<\/h2>\n

    If you don’t already have an SQL project representing your database, you’ll want to create one now. You can create an SQL project from a database using either the SqlPackage CLI or one of the IDEs (VS Code, SQL Server Management Studio, Visual Studio). In VS Code, the object explorer database context menu has convenient entries for creating and updating projects from a database.<\/p>\n

    \"Create<\/a>
    SQL projects integrated directly with the object explorer in VS Code<\/figcaption><\/figure><\/p>\n

    Move tables between schemas<\/span><\/h2>\n

    Creating a new schema in a SQL project is fundamentally adding a new.sql file with the create schema statement in it. Using VS Code, the database projects view includes a menu option for adding an item where several templates are available.<\/span><\/p>\n

    Selecting <\/span>Add item…<\/span><\/strong><\/span> from the SQL project context menu opens this template selection:<\/span><\/p>\n

    \"VS<\/a>
    SQL project new item templates in VS Code<\/figcaption><\/figure><\/p>\n

    Using the option for “Schema”, we enter the name “ProductCatalog” to generate the templated CREATE SCHEMA<\/code> statement immediately in our project.<\/p>\n

    With a new schema for <\/span>[ProductCatalog]<\/code><\/span>, we’re ready to move the <\/span>SalesLT.ProductDescription<\/code><\/span> table to our new schema. Open the <\/span>ProductDescription.sql<\/span><\/strong><\/span> file in the folder <\/span>SalesLT\/Tables<\/code><\/span> and right-click on the table name in the text editor to open the context menu. Select <\/span>Refactor<\/span><\/strong><\/span> from the menu and <\/span>Move to Schema<\/span><\/strong><\/span> in the next prompt.<\/span><\/p>\n

     <\/p>\n

    \"VS<\/a>
    Database projects view in VS Code with ProductDescription table definition open<\/figcaption><\/figure><\/p>\n

    All the user schemas in the SQL project will be presented as a list for selection and the updates to the project will be presented for review.<\/p>\n

    \"Schema<\/a>
    Select a destination schema<\/figcaption><\/figure><\/p>\n

    The review of the changes includes modifying the table definition, any references in additional objects, and the creation\/modification of a refactorlog file.<\/p>\n

    \"Preview<\/a>
    Preview the changes required to wholistically refactor an object in the SQL project<\/figcaption><\/figure><\/p>\n

    We can choose to <\/span>Apply<\/span><\/strong><\/span> or discard and cancel the changes. The refactorlog file included in the changes will keep track of changes where the name or schema of an object is changed, ensuring that each deployment is able to track if the movement has been applied. When you commit your project to source control, you should include the <\/span>ProjectName.refactorlog<\/span><\/strong><\/span> file.<\/span><\/p>\n

    Rename a table<\/span><\/h2>\n

    Another operation that the SQL projects refactoring capability handles well is renaming objects, including tables and columns. Using the refactorlog for table and column renames means that the deployment will be completed without requiring data movement.<\/span><\/p>\n

    To rename the <\/span>SalesOrderDetail<\/code><\/span> table to <\/span>SalesOrderLine<\/code><\/span>, we access the <\/span>Rename Symbol<\/span><\/strong><\/span> menu item. Much like moving an object between schemas, this menu item is in the text editor context menu when we click on the name. Renaming and refactoring options can both be accessed from any object name in the SQL project, including when the object is referenced. For example, a table column can be renamed from the view definition file.<\/span><\/p>\n

    \"VS<\/a>
    Rename an object from the context menu<\/figcaption><\/figure><\/p>\n

     <\/p>\n

    When we rename an object, we can immediately apply the changes or preview the rename across the SQL project. Both options will also include updates to the <\/span>ProjectName.refactorlog<\/span><\/strong><\/span> file. After the move schema and rename operations we’re applied to the SQL project, our refactorlog contents are:<\/span><\/p>\n

    <?xml<\/span> version=\"1.0\" encoding=\"utf-8\"?><\/span><\/span>\r\n<<\/span>Operations<\/span> Version<\/span>=\"1.0\"<\/span> xmlns<\/span>=\"http:\/\/schemas.microsoft.com\/sqlserver\/dac\/Serialization\/2012\/02\"<\/span>><\/span><\/span>\r\n \u00a0<<\/span>Operation<\/span> Name<\/span>=\"Move Schema\"<\/span> Key<\/span>=\"8f599e45-64ea-4636-a369-139cde876a84\"<\/span> ChangeDateTime<\/span>=\"07\/27\/2026 17:06:25\"<\/span>><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ElementName\"<\/span> Value<\/span>=\"[SalesLT].[ProductDescription]\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ElementType\"<\/span> Value<\/span>=\"SqlTable\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"NewSchema\"<\/span> Value<\/span>=\"ProductCatalog\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"IsNewSchemaExternal\"<\/span> Value<\/span>=\"False\"<\/span> \/><\/span><\/span>\r\n \u00a0<\/<\/span>Operation<\/span>><\/span><\/span>\r\n \u00a0<<\/span>Operation<\/span> Name<\/span>=\"Rename Refactor\"<\/span> Key<\/span>=\"4f955015-b69e-4c8e-b527-761612505adb\"<\/span> ChangeDateTime<\/span>=\"07\/27\/2026 17:10:38\"<\/span>><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ElementName\"<\/span> Value<\/span>=\"[SalesLT].[SalesOrderDetail]\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ElementType\"<\/span> Value<\/span>=\"SqlTable\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ParentElementName\"<\/span> Value<\/span>=\"[SalesLT]\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"ParentElementType\"<\/span> Value<\/span>=\"SqlSchema\"<\/span> \/><\/span><\/span>\r\n \u00a0 \u00a0<<\/span>Property<\/span> Name<\/span>=\"NewName\"<\/span> Value<\/span>=\"SalesOrderLine\"<\/span> \/><\/span><\/span>\r\n \u00a0<\/<\/span>Operation<\/span>><\/span><\/span>\r\n<\/<\/span>Operations<\/span>><\/span><\/span><\/pre>\n
    Deploying our changes<\/span><\/h2>\n
    The refactorlog itself is integrated into the <\/span>.dacpac<\/code><\/span> file on project build, and as a result it will be included as part of the deployment whether we’re working in VS Code, another IDE, or an automated CI\/CD environment.<\/span><\/p>\n
    From VS Code, we can use the <\/span>Generate Script<\/span><\/strong><\/span> option to examine the syntax that would be run to apply our changes. The use of refactorlog includes a deployment-managed table <\/span>dbo.__RefactorLog<\/code><\/span>, which will be created by the deployment if it does not already exist. This minimal table tracks the operation keys from the refactorlog when they are applied and enables deployments across multiple iterations of a SQL project to operate seamlessly even when refactoring is involved.<\/span><\/p>\n
    IF<\/span> OBJECT_ID(<\/span>N<\/span>'dbo.__RefactorLog'<\/span>)<\/span> IS<\/span> NULL<\/span><\/span>\r\nBEGIN<\/span><\/span>\r\n \u00a0 \u00a0CREATE<\/span> TABLE<\/span> [<\/span>dbo]<\/span>.[<\/span>__RefactorLog]<\/span> (<\/span>OperationKey UNIQUEIDENTIFIER NOT<\/span> NULL<\/span> PRIMARY<\/span> KEY<\/span>)<\/span><\/span>\r\n \u00a0  EXEC sp_addextendedproperty N<\/span>'microsoft_database_tools_support'<\/span>,<\/span> N<\/span>'refactoring log'<\/span>,<\/span> N<\/span>'schema'<\/span>,<\/span> N<\/span>'dbo'<\/span>,<\/span> N<\/span>'table'<\/span>,<\/span> N<\/span>'__RefactorLog'<\/span><\/span>\r\nEND<\/span><\/span><\/pre>\n
    If you look through the generated deployment script, you’ll also notice the use of <\/span>sp_rename<\/code><\/span> and <\/span>ALTER SCHEMA ... TRANSFER<\/code><\/span> for the refactor operations that need to be applied in that environment. A section of the deployment script is show below, including the print statements that reference specific refactorlog keys.<\/span><\/p>\n
    ...\r\nCREATE SCHEMA [ProductCatalog];\r\n\u200b\r\n\u200b\r\nGO\r\nPRINT N'The following operation was generated from a refactoring log file 8f599e45-64ea-4636-a369-139cde876a84';\r\n\u200b\r\nPRINT N'Move object [SalesLT].[ProductDescription] to different schema [ProductCatalog]';\r\n\u200b\r\n\u200b\r\nGO\r\nALTER SCHEMA [ProductCatalog] TRANSFER [SalesLT].[ProductDescription];\r\n\u200b\r\n\u200b\r\nGO\r\nPRINT N'The following operation was generated from a refactoring log file 4f955015-b69e-4c8e-b527-761612505adb';\r\n\u200b\r\nPRINT N'Rename [SalesLT].[SalesOrderDetail] to SalesOrderLine';\r\n\u200b\r\n\u200b\r\nGO\r\nEXECUTE sp_rename @objname = N'[SalesLT].[SalesOrderDetail]', @newname = N'SalesOrderLine', @objtype = N'OBJECT';\r\n...<\/code><\/pre>\n
    These same statements are used if the deployment is run as a direct <\/span>Publish<\/span><\/strong><\/span> operation. Because the refactorlog is packaged into the <\/span>.dacpac<\/code><\/span> file, you’re able to use refactorlogs in CI\/CD pipelines without any changes to existing artifact publishing and archival.<\/span><\/p>\n
    Once the deployment is completed, our original database is updated to match the renamed table “SalesOrderLine” and the table “ProductDescription” has been moved to the “ProductCatalog” schema.<\/p>\n
    \"Database<\/a>
    Updated database with our refactoring changes<\/figcaption><\/figure><\/p>\n
    Learn more<\/span><\/h2>\n
    Get to know the SQL Database Projects extension in VS Code through the overall SQL projects documentation (<\/span>https:\/\/aka.ms\/sqlprojects<\/a><\/span>) and the SQL Database Projects extension <\/span>getting started guide<\/span><\/a><\/span>.<\/span><\/p>\n
    Learn more about refactoring in SQL projects from the SQL projects <\/span>concepts documentation<\/span><\/a> on refactoring<\/span>.<\/span><\/p>\n
     <\/p>\n
    \n","protected":false},"excerpt":{"rendered":"
    SQL database projects are a fundamental tool for keeping your database in source control, tracking changes and collaborating on quickly evolving database needs. Not to be forgotten, their superpower is being able to dynamically generate deployment scripts that match the declared state and the exact environment you’re deploying to. SQL project development becomes more challenging […]<\/p>\n","protected":false},"author":46830,"featured_media":7380,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[92,625],"tags":[],"class_list":["post-7371","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devops","category-vs-code"],"acf":[],"blog_post_summary":"
    SQL database projects are a fundamental tool for keeping your database in source control, tracking changes and collaborating on quickly evolving database needs. Not to be forgotten, their superpower is being able to dynamically generate deployment scripts that match the declared state and the exact environment you’re deploying to. SQL project development becomes more challenging […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7371","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/46830"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=7371"}],"version-history":[{"count":1,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7371\/revisions"}],"predecessor-version":[{"id":7390,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/7371\/revisions\/7390"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/7380"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=7371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=7371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=7371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}