How can I get table descriptions from Azure SQL Database

2
votes

For SQL Server, I can get table descriptions from the metadata using:

SELECT 
    OBJECT_SCHEMA_NAME(t.object_id) as SchemaName, 
    t.name AS TableName, 
    ex.value AS Description 
FROM 
    sys.tables AS t,
    sys.extended_properties AS ex 
WHERE 
    ex.major_id = t.object_id 
    AND ex.minor_id = 0 
    AND ex.name = 'MS_Description' 
    AND ex.value IS NOT NULL

But that throws an exception hitting an Azure SQL Database. How can I pull it from Azure SQL Database?

The exception I am getting is:

System.Data.SqlClient.SqlException occurred HResult=-2146232060
Message=Invalid object name 'sys.extended_properties'. Source=.Net SqlClient Data Provider ErrorCode=-2146232060 Class=16
LineNumber=1 Number=208 Procedure=""
Server=tcp:odjidszumt.database.windows.net State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at net.windward.utils.ado.SqlServer.WrSqlServerDatabase.TableDesc(DbConnection conn, String select) in c:\vso\Jenova\team\refactoring\Engine\DotNetEngine\Kailua\net\windward\utils\ado\SqlServer\WrSqlServerDatabase.cs:line 465 InnerException:

I have no idea what version of Sql Database - we created a Sql Database on Azure and didn't do anything special so I'm guessing the latest.

2
tsqlazureazure-sql-database
Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged – marc_s
Your query worked for me in Azure SQL. Which server version of Azure SQL are you using? Does the SQL user have access to query the Master database? – Francois
@Francois - sorry no idea what version of the DB. We just created an Azure instance. All the other metadata queries work so we do have access to the master database. It's just the table & column description queries, that both use sys.extended_properties in the select, that fail. – David Thielen

2 Answers

2
votes

Officially, according to this MSDN article, sys.extended_properties view is not supported in Azure SQL Database. The error message you provided says "Invalid object name 'sys.extended_properties'", which proved it's not supported. enter image description here

However, the weird thing is that when I run the query from SSMS and SQL Server Object Explorer against an Azure SQL Database, it works. I then go back to the portal and notice that I created a V12 SQL Server, I then tried creating a V2 SQL Server and run the query against it, get the same result "Invalid object name 'sys.extended_properties'", see below snapshot:
enter image description here

So as per above test, I think 'sys.extended_properties' is only supported in Azure SQL Server V12 database. It seems MS official article might not be updated to the lastest. I'd suggest you check which version of Azure SQL Database you've created:
enter image description here

You can enable V12 when creating the SQL Server as below:
enter image description here

0
votes

Found the Azure sys tables. I needed a solution to set columns requiring case sensitive comparison during a meta driven etl process. NOTE: might want to convert the d.[Value] field to something meta-model ingestible.

/*********************************
Returns Table Column Descriptions
*********************************/
Select 
   s.[name] AS SchemaName
  ,t.[name] AS TableName
  ,c.[name] AS ColumnName
  ,d.[value] AS Desription
From sys.schemas AS s
Inner Join sys.sysobjects AS t /* Tables*/
   On t.[uid] = s.[schema_id]
Inner Join sys.syscolumns AS c
   On c.id = t.id
Inner Join sys.extended_properties AS d /*Column Description*/
   On d.major_id = t.id
  And d.minor_id = c.colid
Where d.[name] = 'MS_Description'