Get table properties in Azure SQL Database

Articles, Tech / By

Even in Azure SQL Database, you need to know certain things about your tables.  For instance, you need to audit table size and row count information, as well as schemas and more.

Data not available

Recently, I found that I could only get some of the properties I wanted when I tried to get table properties from Azure SQL Database using SMO.  I could get table name, schema, and many more, but some random properties – like RowCount and DataSpaceUsed – returned NULL.

After racking my brain for about half a day, I found the problem.  Before getting into that however, let’s get into some sample code that’ll demonstrate the problem:

[reflection.assembly]::loadwithpartialname("Microsoft.SqlServer.Smo") | Out-Null

	$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") 

	$srv.ConnectionContext.ServerInstance = "myserver.database.windows.net"
	$srv.ConnectionContext.LoginSecure = $false;
	$srv.ConnectionContext.set_Login("sean");
	$srv.ConnectionContext.set_Password("WouldntYouLikeToKnow")

foreach ($cDB in ($srv.Databases | ?{ $_.Status -eq "Normal" }))
{
	$currDB = $cDB.Name;
	foreach ($Table in $cDB.Tables)
	{
		$Name = $Table.Name;
		$RowCount = $Table.RowCount;
		"$Table - $RowCount"
	}
}

Simple solutions

The fix turns out to be simple: move to SQL Server 2016.  Something in previous versions of SMO prevents them from reading these properties from Azure databases.  (I’m always able to pull that data just fine from on-premises servers, though.)

But if you’re on SQL 2014, how do you get the 2016 SMO objects?  The easiest way is to download the 2016 Feature Pack.  It won’t say “SMO” anywhere, and there are a lot of packages to choose from.  What you’re looking for is SharedManagementObjects.msi.

Also, starting in SQL 2017, SMO is available as a separate NuGet package.

So even though you’ve got your data in Azure SQL Database, you don’t have to throw your hands up to all admin activity.  Many companies say they don’t need DBAs any more, because their data is in the cloud.  Nothing could be further from the truth.  And hopefully this will get you one step closer to where you need to be with monitoring those environments.

Here’s another article on the need for monitoring your environment, and not just performance.