Visual Studio 2019 SQL Server Data Tools - using code referencing system tables/views problem

Butler Jonathan 61 Reputation points
2021-10-26T15:45:06.957+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-26T16:19:40.467+00:00
    0 comments
  2. Butler Jonathan 61 Reputation points
    2021-10-26T16:28:30.51+00:00

    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)) )
  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-10-27T05:48:33.623+00:00

    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.

    0 comments