0

I use an Oracle database, where I have a database-Link to a Microsoft SQL Server database. I need to access the comments for tables in the Microsoft SQL Server database from my Oracle database.

Using the script below I get the values for owner, table_name and column_name, but my comments field is empty (null), although there should be comments.

Why can't I query the comments?

select owner, table_name, table_type, comments
from all_tab_comments@DB_LINK_SQL_SERVER;

select owner, table_name, column_name, comments
from all_col_comments@DB_LINK_SQL_SERVER;
PercivalPersephi
  • 1
  • I would be surprised if that oracle-specific function was aware of the extended properties used in SQL Server. If correct, then you need TSQL specific syntax. – SMor Mar 23 '22 at 17:51

1 Answers1

0

ALL_TAB_COMMENTS and ALL_COL_COMMENTS are Oracle views. They have no knowledge of SQL Server tables. You would need to create a view on SQL Server. See Stackoverflow SQL Server: Extract Table Meta-Data (description, fields and their data types) and Accessing table comments in SQL Server

Brian Leach
  • 2,025
  • 1
  • 11
  • 14
  • Hey! Thanks, yes, I guess creating a view in SQL Server that I then query via Oracle is the way to go. I'm just wondering why it's possible to query owner, table_name and column name from ALL_TAB_COMMENTS and ALL_COL_COMMENTS, but not comments. – PercivalPersephi Mar 24 '22 at 10:44
  • @PercivalPersephi Ok, you left me curious with that last comment, so I tried to find out what is going on. I traced a session where I `SELECT * FROM all_tab_comments@hssql520.world` . The underlying call was to an undocumented table function `sys.hs$_ddtf_opttables `. The comments are coming from "remarks" column `CAST (t.remarks AS VARCHAR2 (4000)) comments`. I tried adding a variety of extended properties to the SQL Server table (COMMENT, COMMENTS,MS_Description', remarks) but could not get a return value for comments. – Brian Leach Mar 24 '22 at 18:14