Perhaps this is not even possible but I am interested in seeing all Tables across all my Azure SQL DB Databases.
I can use sys.databases to get a list of Databases and sys.tables to get a list of Tables but cannot seem to figure out the correct combination to return Tables per Database.
Is this possible in Azure using straight T-SQL? If not, is there a practicable alternative?
We can use straight T-SQL in Azure SQL DB.
According to your requirements, I have some demos for your reference:
Get all tables in a specify data base:
/// <summary>
/// get all Tables in a data base
/// </summary>
/// <param name="DataBaseName">For example: MyDataBase1</param>
/// <returns></returns>
public ActionResult QueryAllTables(string DataBaseName)
{
ContentResult content = new ContentResult();
SqlConnection conn = new SqlConnection("Server=tcp:dotxxxxxxx.database.windows.net,1433;Initial Catalog="+ DataBaseName + ";Persist Security Info=False;User ID=xxxx;Password=xxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
try
{
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "select id,name from sysobjects where xtype='U'";
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string tableInfo = "id:"+reader[0].ToString()+ " name:"+reader[1].ToString();
content.Content += tableInfo + "\r\n";
}
}
else
{
content.Content = "No Table";
}
}
catch (Exception ex)
{
content.Content = ex.Message;
}
return content;
}
Screenshot of result:
Get all tables from each data base
/// <summary>
/// get all data bases' names
/// </summary>
/// <returns></returns>
public List<string> QueryAllDbName()
{
ContentResult content = new ContentResult();
List<string> list = new List<string>();
using (SqlConnection conn = new SqlConnection("Server=tcp:xxxxxxx.windows.net,1433;Initial Catalog=DotNetAppSqlDb20180410043804_db;Persist Security Info=False;User ID=xxxxx;Password=xxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
try
{
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = "SELECT Name FROM SysDatabases ORDER BY Name";
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string DbName = reader[0].ToString();
list.Add(DbName);
}
}
else
{
list = null;
}
}
catch (Exception ex)
{
content.Content = ex.Message;
}
}
return list;
}
public ActionResult QueryAllTablesFromEachDb()
{
ContentResult content = new ContentResult();
List<string> DbNames = QueryAllDbName();
foreach (string DbName in DbNames)
{
using (SqlConnection conn = new SqlConnection("Server=tcp:xxxxxxxxx.database.windows.net,1433;Initial Catalog="+ DbName + ";Persist Security Info=False;User ID=xxxxxx;Password=xxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
{
try
{
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
content.Content += "DataBase Name: " + DbName + "\r\n";
command.CommandText = "select id,name from sysobjects where xtype='U'";
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
string tableInfo = " Table id:" + reader[0].ToString() + " Table name:" + reader[1].ToString();
content.Content += tableInfo + "\r\n";
}
}
reader.Close();
}
catch (Exception ex)
{
content.Content = ex.Message;
}
}
}
return content;
}
Screenshot of result:
