question

ButlerJonathan-2222 avatar image
0 Votes"
ButlerJonathan-2222 asked ButlerJonathan-2222 commented

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

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ButlerJonathan-2222 avatar image
0 Votes"
ButlerJonathan-2222 answered ButlerJonathan-2222 commented

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)) )




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The error you are getting is on:
[D365-DATAV].INFORMATION_SCHEMA.COLUMNS

You cannot use database references like that in SSDT. You must add a reference and use a database variable.

0 Votes 0 ·

Database variables prevent scripts from being executable as is.

Other Database References between databases work without the use of variables.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.