Sharing common SQL tables across different SQL Server Database projects / git repos

What would be the best solution/design for sharing common SQL tables definitions between different projects whilst using SQL Server Database projects deployed as DacPacs?

For example:

Master Project sqlproj
 - CommonTable1.sql

Sub Project 1 sqlproject
 - P1CustomTable1.sql

Sub Project 2 sqlproject
 - P2CustomTable1.sql
 - P2CustomTable1.sql

All sub-projects should contain CommonTable1.sql. Ideally in a single SQL Server Database project (sqlproj) per sub-project.

Key points/goals:

• All projects should be self-contained and in their own repositories and build pipelines
• Full CI/CD with Azure DevOps / build pipelines
• Seamless when working on sub-projects. Bring a package in and forget about it. Updates just update NuGet packages if/where wanted.
• Ease of updating Local SQL development env (ideally right click sqlproj and publish
• Avoiding EF migrations (but could be the solution? If the master project changes in the future, subprojects should be able to easily bring in the changes.

Option 1

I liked the idea of having a Master.DbSchema package that can be included in sub-projects sqlproj via NuGet. But this doesn’t seem fully possible as Sqlproj does not allow for Nuget references (still .Net Framework 4.8 / Standard 2.0).

I explored having a separate c# Class Library (not sqlproj) with all SQL files defined. Then used prop files etc.. and nuget to bring them into sub-projects. Similar to the answer here - How to flatten Nuget package contentFiles?

This allowed for sub-projects to add the files into sqlproj but only via linked files in another new class library that I could reference the Master.DbSchema in. This wasn’t ideal for a few reasons. 1 you lose compilation validation of the SQL files when not in sqlproj, 2. It required a middle library for each sub-project, 3. it was easy to accidentally edit linked files and the middle library. 4. Lots of editing of proj files – felt hacky.

Option 2

I've fumbled my way around having a dacpac generated from Master 1 and packaged that up into a NuGet feed. This could then be added separately in the sub-project's yaml files to deploy both the master and the sub-project dacpacs. This took some hacking around of sub-projects libraries to include build output from the NuGet pgk (see - How to deploy dacpac file from a nuget package in c#). Again doesn’t feel right

Option 3

I'm still not happy with the approach and now thinking Git submodules I'm sure this will have similar issues and submodules come with their own maintenance headache. Will likely have the same outcome in that SQL files contained within a sqlproj file for compile time validation?

My Conclusion

The overall design is probably off until such a time when SQL Database project support .Net Core and NuGet packages. Do I reinvest in making the master and all sub-projects use Code migrations? Am I'm missing something simple?

EDIT: Context

To add a bit more context to what I’m trying to achieve.

Assume I have 5 applications/solutions all with identical tables. Audit, Activity for example … but some do also include their own unique objects.

Each solution has its own sqlproj, its own DbContext, Models, ViewModels, Validation etc.. most of which are duplicated and sometimes out of sync.

There is an existing Master project that is already referenced in all 5 projects.

I was hoping to cut out all the repetitive code and include Common features into a master project and keep only project-specific things for each sub-project.