When we decided to use T-SQL and a Fabric Data Warehouse for our ETL, I started thinking about generating the code with the metadata in the system catalog views or the INFORMATION_SCHEMA. Having done this sort of thing before in SQL Server over the years, it seemed really straightforward. And it kind of is, until it isn’t.

In this blog I want to show you a few ways you need to understand how working with metadata and temp tables varies (sometimes wildly) from the comfortable SQL Server environment and language you know very well, and give tips on how to get around these differences.

In a later post, I will share some of the differences between the metadata on each platform (and how sometimes they use SQL Server terms where they don’t exactly apply), but this blog is about some of the difficulties of working with it. And I didn’t really understand it until I finally grokked how temp tables worked.

Base Metadata

At the base of it, metadata looks very similar. Create a table like the following:

--Just like in SQL Server, CREATE SCHEMA must be the only thing in the query.
CREATE SCHEMA SchemaName;
GO 
--also, GO seems to work like it did in SQL Server.
CREATE TABLE SchemaName.TableName
(
    TableNameId int NOT NULL,
    AnotherColumn varchar(200) NOT NULL
);
CREATE TABLE SchemaName.TableName2
(
    TableName2Id int NOT NULL,
    AnotherColumn varchar(200) NOT NULL
);

Nothing surprising. You can use INFORMATION_SCHEMA.TABLES to see the table metadata:

SELECT *
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName';

And unsurprisingly, the output will be a table with the name and types of objects.

You can also use the COLUMNS view to see the columns of your objects

SELECT *
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'SchemaName';

And if you check out the output of this, you can see plenty of details, including the same sort of stuff you can see in SQL Server, including the COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE (which you need to add the lengths and stuff on to your datatype declarations if you want the complete type), COLLATION_NAME, etc.

Working with the metadata

What makes this interesting isn’t the contents of the table, but the fact that this query will fail (and then why it will fail).

SELECT *
INTO   SchemaName.Metadata
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName';

First comes an error that made absolutely no sense the first time I saw it.

Msg 24574, Level 16, State 1, Line 57
The data type 'nvarchar(128)' in column 'TABLE_CATALOG' is
not supported in this edition of SQL Server.

What do you mean? I just queried this data in this edition of SQL Server (which as I mentioned in the last entry in this series… refers to SQL Server, not Fabric).

But let’s do our duty and cast the types. Hopefully one day the metadata will be in a compatible data storage, but I generally understand why it isn’t since data is stored in a much different format than metadata:

SELECT CAST(TABLE_CATALOG AS varchar(128)) AS TABLE_CATALOG,
       CAST(TABLE_SCHEMA AS varchar(128)) AS TABLE_SCHEMA,
       CAST(TABLE_NAME AS varchar(128)) AS TABLE_NAME,
       CAST(TABLE_TYPE AS varchar(128)) AS TABLE_TYPE
INTO   SchemaName.Metadata
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName'; 

And this gives me an error that quickly got on my nerves as I saw it over and over:

Msg 15816, Level 16, State 3, Line 72
The query references an object that is not supported
in distributed processing mode.

Note: While SELECT ... INTO is fully supported, it is limited in how it works compared to the syntax I will use next is far more capable. Since this is a blog about going from SQL Server T-SQL, and I know it is what I used because it is what I knew…

I hadn’t used it when I was actually going through this, but I knew I had heard about CREATE TABLE AS, (even if this would be the first time I tried it). I figured maybe it was the syntax.

CREATE TABLE SchemaName.Metadata 
AS
SELECT CAST(TABLE_CATALOG AS varchar(128)) AS TABLE_CATALOG,
       CAST(TABLE_SCHEMA AS varchar(128)) AS TABLE_SCHEMA,
       CAST(TABLE_NAME AS varchar(128)) AS TABLE_NAME,
       CAST(TABLE_TYPE AS varchar(128)) AS TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName'; 

Yeah, it wasn’t just the syntax.

Msg 15816, Level 16, State 3, Line 88
The query references an object that is not supported
in distributed processing mode.

And, just for demo purposes since I pay myself by the word. I created a table:

CREATE TABLE SchemaName.Metadata
(
    TABLE_CATALOG varchar(128),
    TABLE_SCHEMA varchar(128),
    TABLE_NAME varchar(128),
    TABLE_TYPE varchar(128)
);

Then tried a good old basic INSERT:

INSERT INTO  SchemaName.Metadata
            (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)
SELECT CAST(TABLE_CATALOG AS varchar(128)) AS TABLE_CATALOG,
       CAST(TABLE_SCHEMA AS varchar(128)) AS TABLE_SCHEMA,
       CAST(TABLE_NAME AS varchar(128)) AS TABLE_NAME,
       CAST(TABLE_TYPE AS varchar(128)) AS TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName'; 

And yeah, you probably could have guessed since I didn’t give any positive indications, you knew this was going to happen:

Msg 15816, Level 16, State 3, Line 110
The query references an object that is not supported
in distributed processing mode.

Enter the temporary table.

Ok, so then I started wondering, can I use a temporary table? And IS there a temporary table. Because things change a lot in this area, search engines and AI gave some wonky answers, so I decide to just try it.

CREATE TABLE #TempTableOfTables
AS
SELECT CAST(TABLE_CATALOG AS varchar(128)) AS TABLE_CATALOG,
       CAST(TABLE_SCHEMA AS varchar(128)) AS TABLE_SCHEMA,
       CAST(TABLE_NAME AS varchar(128)) AS TABLE_NAME,
       CAST(TABLE_TYPE AS varchar(128)) AS TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName';

And at this point I started growling in a kind of pain that is different from the back I tweaked last weekend:

Msg 15816, Level 16, State 3, Line 110
The query references an object that is not supported
in distributed processing mode.

I was excited that temp tables clearly existed, but they didn’t solve my previous problem:

CREATE TABLE #Metadata
(
    TABLE_CATALOG varchar(128),
    TABLE_SCHEMA varchar(128),
    TABLE_NAME varchar(128),
    TABLE_TYPE varchar(128)
); 

I can insert data into them:

INSERT INTO #MetaData 
VALUES ('A','A','A','A');

And view the data:

SELECT *
FROM   #Metadata; 

So cool, it works AS expected.

Now I will INSERT data from the same temp table using CTAS:

CREATE TABLE #Metadata2
AS 
SELECT *
FROM   #Metadata;

Ok, so I can’t do that. (Pretty sure this has to do with getting the metadata to recreate the temp table being an issue.)

Msg 448, Level 16, State 9, Line 173
Invalid collation 'SQL_Latin1_General_CP1_CI_AS'.

But I can insert data from another temp table:

CREATE TABLE #Metadata2
(
    TABLE_CATALOG varchar(128),
    TABLE_SCHEMA varchar(128),
    TABLE_NAME varchar(128),
    TABLE_TYPE varchar(128)
);
INSERT INTO #Metadata2
SELECT *
FROM   #Metadata; 

I tried to ask Copilot what was wrong and it was remarkably unhelpful as to why, but this version works.

And then it got really weird

Ok, so we have a temp table, but we can’t insert data into it from the INFORMATION_SCHEMA views. But can we from “regular” tables?

CREATE TABLE SchemaName.Source
(
    TABLE_CATALOG varchar(128),
    TABLE_SCHEMA varchar(128),
    TABLE_NAME varchar(128),
    TABLE_TYPE varchar(128)
);
INSERT INTO SchemaName.Source 
SELECT *
FROM   #Metadata; 

Again, no.

Msg 15816, Level 16, State 3, Line 215
The query references an object that is not supported
in distributed processing mode.

Ok, it is time to skip ahead. This was where I started searching for answers about metadata and using it in local tables and I found Koen Verbeeck’s post System Views in Microsoft Fabric – Query references an object that is not supported in distributed processing mode that I will refer you to.

Basically you use a pipeline/copy job to move the data into a distributed table that you can access. This is the path I took, creating a central repository of all my lakehouse and warehouse environments to then decorate some with better names, new datatypes, etc.

Quick note

This pertains to joins too. You can’t cross these boundaries, so you can’t do something LIKE this:

SELECT *
FROM   INFORMATION_SCHEMA.TABLES
         CROSS JOIN SchemaName.Metadata; 

Or you will get the same error that I don’t want to repeat here in case it is giving you angst like it is me at this point even during final editing.

Lastly, what good are temp tables?

I actually don’t know about temp tables as I have demoed them so far have any value. They seem to be just part of the language that came over. Their data is stored in a structure that can’t be used in many ways in the Data Warehouse.

But (and this is a lesson that was really hard for me to learn (I used tables named TEMP_ for temporary tables in a few cases before I learned that there are two kinds of temp tables!) You can read more about this here in the page: Microsoft Documentation on Temp Tables in Fabric DW.

You might say “why wouldn’t you just read the documentation?” Good question. To me it is like any task you think you have done before. I didn’t know I needed to. A regular temp table seemed to work, as does most stuff in Fabric T-SQL, which is the point of this series, to point out the tiny, yet significant different you will encounter.

But declared as in normal T-SQL code, the things we have been doing are not available. But, I did find OUT that there are distributed temp tables. Now they will be accessible in much the same way as they are in SQL Server. So if I take the table create we did earlier with a temp table and add the following:

DROP TABLE IF EXISTS #Metadata;
CREATE TABLE #Metadata
(
    TABLE_CATALOG varchar(128),
    TABLE_SCHEMA varchar(128),
    TABLE_NAME varchar(128),
    TABLE_TYPE varchar(128)
  -------------------------------
) WITH (DISTRIBUTION=ROUND_ROBIN); 
  -------------------------------

That syntax only works with temp tables. It will give an error if the table isn’t a temp table. There is a concept called clustering that I will cover some day (after I learn it! It is related to how you cluster a table in SQL Server which arranges rows in a physical order.

Now, I will make sure the SchemaName.Source table has a row in it.

TRUNCATE TABLE SchemaName.Source 
INSERT INTO SchemaName.Source 
VALUES ('A','A','A','A');
INSERT INTO #Metadata
SELECT *
FROM   SchemaName.Source;
SELECT * 
FROM   #MetaData;

This returns the four rows with just the letter A in them that we just inserted.

So…drum roll please. Will this work?

INSERT INTO #Metadata
   (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)
SELECT CAST(TABLE_CATALOG AS varchar(128)) AS TABLE_CATALOG,
       CAST(TABLE_SCHEMA AS varchar(128)) AS TABLE_SCHEMA,
       CAST(TABLE_NAME AS varchar(128)) AS TABLE_NAME,
       CAST(TABLE_TYPE AS varchar(128)) AS TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_SCHEMA = 'SchemaName'; 

Feeling a bit like Charie Brown trying to kick that dang football every single year...it does not. Of course his documentation did make promises that he would be able to kick it this year… if it had only been notorized.

The metadata is still (or currently if Koen’s article is right that they may fix it, but it was written 2 years ago) inaccessible:

Msg 15816, Level 16, State 3, Line 261
The query references an object that is not supported in distributed processing mode.

Summary

Sometimes things that seem simple are not. This is one of those cases. For the foreseeable future, metadata on your database is not accessible directly to be queries and has to be treated like it is in a different realm.

But once you realize that distributed temp tables can be mingled with regular tables, it isn’t a tremendous issue other than necessitating reaching out and fetching the metadata for a container and pulling it into a distributed table.

Metadata is the only place I have ran into this distributing process mode issue, and by creating my own tables and using a Fabric Copy Job, it isn’t a truly difficult thing to deal with. But it certainly can make for some interesting work when you just want to compare metadata to data stored in your local tables.