Select list of tables in database in Azure or SQL Server, with the same syntax
I need to get the list of table names associated with an Azure database, from within Access.
With SQL Server, I've been using this syntax:
SELECT Table_Schema, Table_Schema + '.' + Table_Name as Table_Name, CASE WHEN TABLE_TYPE = 'Base Table' THEN 'Table' ELSE TABLE_TYPE END as Source
FROM [DatabaseSQL_DEV].INFORMAT
ORDER BY CASE WHEN TABLE_TYPE= 'Base Table' THEN 'Table' ELSE TABLE_TYPE END, Table_Name
But when I run this against an Azure database, I get the error:
If I remove the reference to the database "[DatabaseSQL_DEV]." from the SQL, it returns a list of tables, but these are not in the database I'm interested in.
Is there a syntax for pulling the Schema, Tablename, and Table_Type for a particular database which will work in both SQL (Windows Authentication) and Azure (SQL Authentication)? If not, what would the appropriate syntax be for an Azure database?
With SQL Server, I've been using this syntax:
SELECT Table_Schema, Table_Schema + '.' + Table_Name as Table_Name, CASE WHEN TABLE_TYPE = 'Base Table' THEN 'Table' ELSE TABLE_TYPE END as Source
FROM [DatabaseSQL_DEV].INFORMAT
ORDER BY CASE WHEN TABLE_TYPE= 'Base Table' THEN 'Table' ELSE TABLE_TYPE END, Table_Name
But when I run this against an Azure database, I get the error:
If I remove the reference to the database "[DatabaseSQL_DEV]." from the SQL, it returns a list of tables, but these are not in the database I'm interested in.Is there a syntax for pulling the Schema, Tablename, and Table_Type for a particular database which will work in both SQL (Windows Authentication) and Azure (SQL Authentication)? If not, what would the appropriate syntax be for an Azure database?
No, there's no syntax in Azure that does that. Azure doesn't inherently "know" about any other db than the one in which it's running now.
You can create external data sources in Azure that are in another db (including certain non-SQL ones).
CREATE EXTERNAL DATA SOURCE
CREATE EXTERNAL TABLE
I haven't tried this for system tables.
Either way, I'd strongly recommend getting away from INFO_SCHEMA views and using SQL's native sys. views.
You can create external data sources in Azure that are in another db (including certain non-SQL ones).
CREATE EXTERNAL DATA SOURCE
CREATE EXTERNAL TABLE
I haven't tried this for system tables.
Either way, I'd strongly recommend getting away from INFO_SCHEMA views and using SQL's native sys. views.
ASKER
So, in Azure, do I have to specify the database name in the SQL connection string?
OK, Scott, then what sys. views should I use to do this in SQL Server and in Azure?
Dale
OK, Scott, then what sys. views should I use to do this in SQL Server and in Azure?
Dale
Hi,
sys.tables appears to give just the tables, and not the views.
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-2017
There are links at the bottom that may help. The navigation tree at the left shows sys.views.
HTH
David
sys.tables appears to give just the tables, and not the views.
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-2017
There are links at the bottom that may help. The navigation tree at the left shows sys.views.
HTH
David
sys.objects will contain all objects, so use that as a base.
Then you can use columns within that to limit the output to tables and views:
SELECT ...
FROM sys.objects o
INNER JOIN ...
WHERE o.type_desc IN ('USER_TABLE', 'VIEW')
Then you can use columns within that to limit the output to tables and views:
SELECT ...
FROM sys.objects o
INNER JOIN ...
WHERE o.type_desc IN ('USER_TABLE', 'VIEW')
ASKER
not helpful, Scott.
SELECT O.*
FROM sys.objects as o
ORDER BY Type_desc
does not return any of the tables or views in 7 diffferent databases on the selected server.
SELECT O.*
FROM sys.objects as o
ORDER BY Type_desc
does not return any of the tables or views in 7 diffferent databases on the selected server.
Interesting. The only logical reason for that would be if you didn't have permission to see it.
You can stick with INFO_SCHEMA views. Just be aware that they are slower and often cause blocking. Yes, I know the definitions of the views don't show that, but I've seen it over and over in our real-life situations.
You can stick with INFO_SCHEMA views. Just be aware that they are slower and often cause blocking. Yes, I know the definitions of the views don't show that, but I've seen it over and over in our real-life situations.
ASKER
Well, for the particular server i was looking at, it is on my personal PC and I am logged in as the admin, so there is nothing on that server that I shouldn't have permissions for. When I run:
SELECT Table_Schema
, Table_Schema + '.' + Table_Name as Table_Name
, CASE WHEN TABLE_TYPE = 'Base Table' THEN 'Table' ELSE TABLE_TYPE END as Source
FROM [FlexReport].INFORMATION_S
ORDER BY CASE WHEN TABLE_TYPE= 'Base Table' THEN 'Table' ELSE TABLE_TYPE END, Table_Name
I get 4 tables and two views.
I'm sure there is a way to do this,
Are there any particular databases (master, msdb, ...) that I should be mapped to and what database roles should I be assigned?
SELECT Table_Schema
, Table_Schema + '.' + Table_Name as Table_Name
, CASE WHEN TABLE_TYPE = 'Base Table' THEN 'Table' ELSE TABLE_TYPE END as Source
FROM [FlexReport].INFORMATION_S
ORDER BY CASE WHEN TABLE_TYPE= 'Base Table' THEN 'Table' ELSE TABLE_TYPE END, Table_Name
I get 4 tables and two views.
I'm sure there is a way to do this,
Are there any particular databases (master, msdb, ...) that I should be mapped to and what database roles should I be assigned?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
This works on SQL Azure
select *
from INFORMATION_SCHEMA.TABLES
;
You don't get to specify the database on Azure - the use databasename go fails ...
HTH
David