MS SQL Server SELECT not showing all fields in tables, how to fix?

Question

1. We use an Azure Synapse SQL server, which contains a copy of our data from a Postgresql server, and I'm trying to list all the table names and fields in each table and display them in a viz in Power BI. Since I'm doing work in PBI this makes it convenient to see if I have the right name of the field.
2. I'm using PBI from April 2024.
3. I do not have admin access to that SQL server, which is an MS SQL Server so I will have to ask someone else to make any permissions changes.
4. Since this seems to be an Azure SQL issue I thought I would ask here. Other PBI queries work fine.

Here is the SQL we use to connect and get the table and field names.

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS;

I have a table visualization (viz) in PBI which shows the table names and field names and info about each field. I can filter based on the table name.

Some tables do not show all the fields in this field list. Let's say for "table1" the field list does not show the field "desc". But when I do another query on "table1" I can use the field "desc" with no errors.

This problem is only on the Azure SQL server. When I query the Postgresql server direct it shows all the fields for a table just fine. But we are required to use the Azure SQL server to reduce load on the Postgresql server.

• How do I fix this?
• Could this be a permissions issue?

Thank you.

Answer 1

Yes, it could be permissions, see

https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver16

=> Returns one row for each column that can be accessed by the current user in the current database.