While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server – parents are installed before children so there are no problems during installation. On the other hand separate files are good because they can be versioned and changed objects (tables, view, procedures) can be easily identified – but dependency ordering is lost.
Is there a way to have best of both worlds, that is have all objects in separate files and at the same time preserve dependencies ordering? I am asking about existing schema that needs to be versioned after years of neglecting, not about best practices for starting new project.
Best Answer
The best way to get this done is to use a tool to manage the process of getting stuff in and out of source control and then deploying it.
If you have an MSDN license, Visual Studio has a database project that can do this. It's free with the license and works pretty well. It has some limitations, especially when it comes to dealing with code that can lead to data loss. In those cases you may find manual intervention is necessary. Also, you'll have to do some of your database work within Visual Studio because there's no way to use Azure Data Studio or SQL Server Management Studio. However, this approach solves the dependency issue.
There are also third party solutions. I work for a vendor, Redgate, that makes a few of them. We solve the data loss problem and we work within SSMS (and soon ADS). There are other vendors as well (I just think we're better). These tools also solve the dependency problem while still letting you have individual files so that you can track all changes independently through your source control management tool.