Is there a way to query a SSDT project to get a list of tables or foreign keys? Similar to what you can do with the actual database?
Does MS provide a way to query a SSDT project
ssdt
Related Solutions
This should be handled by adding a reference to the master database.
Previously, we've had issues related to the relative path that's used to reference the master.dacpac (as described here). If I remember correctly, we pulled a copy of the master.dacpac file into the local project and referenced it using the last option shown in the dialog above. This is not ideal however as you'll need to remember to update your copy of the reference periodically.
Will update if I can find the details of what was causing a problem with the referencing method.
I've got the same problem when using the Schema Compare tool in Visual Studio 2013. From what I can tell, the reason it wants to keep deploying the SQLCLR assemblies is the Module Version ID (MVID) in the DLL. The MVID is a guid that is automatically generated by the C# compiler during compilation so each rebuild get the DLL a new MVID.
One option is to try and avoid a rebuild of the DLLs. If you're building locally with Visual Studio, you'll have to avoid the "clean" and "rebuild" options. If you're using a build server such as TFS you'll need to configure the build to not clean the workspace.
The other option that I'm going to try is to have the C# code in a separate project, and "import" the DLLs into the main database project as and when the C# code changes. Pretty much as I used to do when using the older style "DataDude" projects. Using the latest version of SSDT for VS2013, I've added the SQLCLR assembly DLL as a reference in the main SQL Server project, and set the "Model Aware" property to "True". This properties seems to be required to allow me to reference the assembly in the store procedure / function DDL statements.
Related Question
- Excluding a specific table from a SSDT deploy
- SSRS – Specify Windows Account for SSRS Data Source in SSDT Project
- SSDT – How to Refresh Reference to Objects in Visual Studio
- Visual Studio SSDT Unresolved Reference to SSISDB – How to Fix
- How to avoid dropping schemas when publishing DB in SSDT Database project
- SSDT for Visual Studio 2017 – Missing Integration Services
Best Answer
YES! You can query the output to an SSDT project called a dacpac using the DacFx.
The easiest way is to grab this sample project from microsoft (https://github.com/microsoft/DACExtensions/tree/master/DacFxStronglyTypedModel) and build the "DacExtensions" dll then reference that from your own code (c#, powershell etc.)
When you use the DacExtensions you can then you can ask for a list of tables or foreign keys, have a look at this:
https://stackoverflow.com/questions/40747082/finding-data-type-information-for-certain-objects-in-dacfx-api
The question shows how to get the TSQLModel and the answer has how to get a list of tables.
Good luck!