Using references to system tables and views within your database project.

In November I had the honour of being able to present my session on how to integrate your database deployment in your CI/CD pipeline. It was the first time I gave the session and other than needing 3-5 extra minutes the session went really well.

One of the attendees asked me a question, which got me thinking…. “how do you incorporate system tables and views within your solution, given that for some reason the compiler gives errors whenever trying to build the dacpac”.

So here goes…

Problem: When referencing system tables within stored procedures, views or any scripts for that matter the compiler gives an error.

So let’s start with a simple project. It just contains a single table called person with the following definition:

It gives us the following table definition in SSMS:

Now that we can publish a database, let’s try adding a reference to a system table.

For this example I will create a simple stored procedure that will give us a listing of the columns for any given table (that exists obviously) and their data types :

The Resultset is the result of the following query:

DECLARE @TableName sysname = 'dbo.Person';

SELECT    x.ObjectSchemaName
      , x.ObjectName
      , x.ObjectFullName
      , x.ColumnName
      , x.ColumntypeName
FROM
        (
            SELECT    OBJECT_SCHEMA_NAME(c.object_id)    AS ObjectSchemaName
                  , OBJECT_NAME(c.object_id) AS ObjectName
                  , OBJECT_SCHEMA_NAME(c.object_id) + '.' 
                    + OBJECT_NAME(c.object_id) AS ObjectFullName                    
                  , c.name AS ColumnName
                  , t.name AS ColumntypeName
                  , c.column_id AS ColumnOrder
            FROM    sys.columns c
            JOIN    sys.types    t ON c.system_type_id = t.system_type_id
            WHERE
                    c.object_id = OBJECT_ID(@TableName)
        ) x
ORDER BY
        x.ColumnOrder ASC;

First, let’s define the SP in SSMS as follows:

And when running this for dbo.Person, it gives us the following result:

OK. Easy peasy... Let's port this definition into our database project, and that Is where you will see some differences compared to SSMS.

As you can see the compiler gives you warnings that there are unresolved references to the objects sys.columns and sys.types. Although they are classified by the compiler as warnings, you really should be treating them as an error as later, during your build, you will encounter issues and your build will fail. So you should resolve them.

I have searched high and low, but cannot seem to find an “out-of-the-box” solution, So I came up with this scenario: create views of the system tables (on a separate schema) within your database and use them in your code, but within the database project you arrange pre and post deployment scripts in such a way that the project assumes you are working with standard tables, but in reality these get swapped out during deploy.

Let me document each step, and I am sure you will understand what is going on.

Within SSMS:

1. Create a new schema (I will use “so” (for System Objects))

2. Create a view of the tables (only create the views that you need)

3. Alter your SP to reflect your changes.

Within your Database project do the following:

1. Add the new schema

2. Add a table definition that is a copy of the system tables

3. Alter your SP to reflect your changes.

4. Add a pre-deployment script that drops the view and creates the table

5. Add a post-deployment script that drops the table and creates the view

Within SSMS we run the following code:

CREATE SCHEMA so;
GO

CREATE VIEW so.Columns
AS
SELECT    *
FROM    sys.columns;
GO

CREATE VIEW so.Types
AS
SELECT    *
FROM    sys.types;
GO

ALTER PROCEDURE dbo.usp_GetColumnInformation @TableName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT    x.ObjectSchemaName
          , x.ObjectName
          , x.ObjectFullName
          , x.ColumnName
          , x.ColumntypeName
    FROM
            (
                SELECT    OBJECT_SCHEMA_NAME(c.object_id) AS ObjectSchemaName
                      , OBJECT_NAME(c.object_id) AS ObjectName
                      , OBJECT_SCHEMA_NAME(c.object_id) + '.' 
                        + OBJECT_NAME(c.object_id) AS ObjectFullName
                      , c.name AS ColumnName
                      , t.name AS ColumntypeName
                      , c.column_id AS ColumnOrder
                FROM    so.Columns c
                JOIN    so.Types   t ON c.system_type_id = t.system_type_id
                WHERE
                        c.object_id = OBJECT_ID(@TableName)
            ) x
    ORDER BY
            x.ColumnOrder ASC;
    RETURN 0; --SUCCESS
END;
GO

Executing the Stored procedure gives us the same result:

That was the easy(quick) part. Now add the steps to your project so you get the same result in your deployment.

Add the new schema

Add a table definition that is a copy of the system tables

Alter your SP to reflect your changes.

Add a pre-deployment script that drops the view and creates the table (to be sure that this step does not fail, i try dropping both the table or the view, depending on which exists...)

Add a post-deployment script that drops the table and creates the view(again, to be sure that this step does not fail, i try dropping both the table or the view, depending on which exists...)

And hey presto... Bob’s your uncle!

All relevant files and the solution are available as a zip file from my github repo.

Until next time, take care of yourself and each other!