Accessing table and column comments in Oracle for tables in SQL Server via databaselink

Question

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;

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
– SMor, Commented Mar 23, 2022 at 17:51

Brian Leach · Accepted Answer · 2022-03-24 00:24:54Z

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

answered Mar 24, 2022 at 0:24

Brian Leach

2,1111 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PercivalPersephi Over a year ago

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.

Brian Leach Over a year ago

@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 [email protected] . 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.

Collectives™ on Stack Overflow

Accessing table and column comments in Oracle for tables in SQL Server via databaselink

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related