You add a reference to the master database.
Please see:
https://learn.microsoft.com/en-us/sql/ssdt/add-database-reference-dialog-box?view=sql-server-ver15
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hello
I am creating a Proof of concept for our Devops.....Visual Sutdio using Azure Devops Git repositories. In visual studio we are using SQL Server Data Tools (SSDT) projects to capture our database designs and changes. My problem comes when we have code that references system objects - what is Microsofts' best practice for source controlled artifacts that reference SQL Server system tables and views in the SSDT projects.
I cannot create those system tables and views in projects (for each database using those system tables/views) so I get errors.
Thanks
Jonathan
You add a reference to the master database.
Please see:
https://learn.microsoft.com/en-us/sql/ssdt/add-database-reference-dialog-box?view=sql-server-ver15
Hi Tom
Thanks for your reply.
I did that and it doesn't work.
The T-SQL in question references the INFORMATION_SCHEMA views in two of the SSDT database projects, using two part names in current project and database names, hence 3 part name, to the other database.
Are these queries not possible in SSDT?
Thanks
Jonathan
SELECT Distinct ISNULL(b.TableName,a.ViewName) AS TableName
From
( Select SCol.TABLE_SCHEMA As 'Schema',
SCol.TABLE_NAME As 'ViewName',
SCol.COLUMN_NAME As 'ColumnName',
SCol.DATA_TYPE As 'DataType',
SCol.CHARACTER_MAXIMUM_LENGTH As 'CharLength'
From INFORMATION_SCHEMA.COLUMNS SCol
Where SCol.TABLE_SCHEMA = 'D365DATAV'
) A
Full Outer Join
( Select SCol.TABLE_SCHEMA As 'Schema',
REPLACE(SCol.TABLE_NAME,'BYOD_','') As 'TableName',
SCol.COLUMN_NAME As 'ColumnName',
SCol.DATA_TYPE As 'DataType',
SCol.CHARACTER_MAXIMUM_LENGTH As 'CharLength'
From [D365-DATAV].INFORMATION_SCHEMA.COLUMNS SCol
) b
On A.ViewName = b.TableName
And A.ColumnName = b.ColumnName
Where (A.ViewName Is Null)
Or (IsNull(A.DataType, 'A') <> IsNull(b.DataType, 'A'))
Or (IsNull(A.CharLength, 1) <> IsNull(b.CharLength, 1)) )
Hi ButlerJonathan-2222,
In addition, please check the following links which might be helpful:
Referencing System Tables/View in Other Databases
SQL Server Data Tools (SSDT) and Database References
Best Regards,
Amelia
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.