Deploy database to Azure SQL fails, sp_MSforeachtable not found

Question

I am trying to publish a SQL Server database using a .dacpac and publish profile to an Azure SQL database. We have been using on premises SQL Server with no problems for quite some time, and now we are evaluating Azure and AWS to see which is best suited for our needs.

We have a SQL Server database project and want to deploy it to Azure SQL database, however it fails to execute the script generated by SSDT. This is because the generated script contains a call to the stored procedure sp_MSforeachtable which does not exist in Azure SQL.

I also have changed the target platform from SQL Server 2016 to Azure SQL V12. I am trying this from Visual Studio 2017's publish profile and from VSTS Release management using Azure SQL database deployment task. An of course by providing the necessary .dacpac and publish.xml files in the task.

I know I can manually add the stored procedure, however it doesn't seem that is what is intended by these publishing/deployment methods. Does anyone have any ideas?

Thanks

Answer 1

This feature is not implemented in SQL Database in Azure.

You can find a copy of it at this location: https://gist.github.com/metaskills/893599

Answer 2

sp_MSforeachtable is an undocumented stored procedure in the Master database. This apparently hasn't been ported over to Azure SQL. I believe you'll need to add it manually to the Master database. Once that is done, your DACPAC should work just fine on your own Azure SQL database. I don't see a problem with manually adding sp_MSforeachtable. DACPACs are meant to keep your database in sync, not the Master database.