CODEWITHSUNDEEP
t-sqlazureazure-sql-database
David Thielen
David Thielen

Reputation: 33006

How can I get table descriptions from Azure SQL Database

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.

Upvotes: 2

Views: 4979

Answers (2)

ChrisB
ChrisB

Reputation: 1

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'

Upvotes: 0

forester123
forester123

Reputation: 1579

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

Upvotes: 2

Related Questions