CODEWITHSUNDEEP
sql-serverazureazure-sql-database
CarCrazyBen
CarCrazyBen

Reputation: 1136

Azure SQL Database find all databases associated with specific Table

I am working in Azure SQL Database. I want to find all Tables common to more than one database but have not been able to do so.

I am an "Admin" level User, but when I execute [select * from sys.tables] in the master database I am not getting results for the 7 databases on the Azure server.

What is the method to select ALL Tables from all Databases in Azure SQL Database?

Upvotes: 0

Views: 783

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

Server in Azure is logical server,It is not like Onpremises server..Things are logically grouped for making administration easier..Each database is a separate entity

so you need to execute sys.tables in each database to get data..There is one more feature called managed instance which is just like on premises server..

Check out this link to see which one is better for you

https://joeydantoni.com/2017/10/05/managed-instances-versus-azure-sql-database-whats-the-right-solution-for-you/

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

Run This query to list all the user tables and number of records in each of the table on SQL Azure DB

select t.name ,s.row_count from sys.tables t

join sys.dm_db_partition_stats s

ON t.object_id = s.object_id

and t.type_desc = ‘USER_TABLE’

and t.name not like ‘%dss%’

and s.index_id = 1

Upvotes: 2

Related Questions