Tracing Information Steward metadata tables with SQL Server

[mageshwaran_subramanian]

To build the custom metadata reports , we often need to know where Information Steward stores the metadata in repository database.SAP provides limited information about the Information Steward metadata tables in their documents.One way of tracing where IS metadata stores the information if the repository resides in SQL server is to use Query sys.dm_db_index_usage_stats view.

This is Dynamic Management View in SQL server that keeps track of any table that has been last updated or scanned.

SELECT OBJECT_NAME(OBJECT_ID) AS Table_Name, last_user_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'DB_IS_Repo')

ORDER BY last_user_update desc

*since you are querying a system view , make sure you have the necessary rights to access the view.

You can also use SQL server's profiler for the tracing (I haven't tried this approach).

To use this approach you need to perform the action that you are interested in and run the query. As an example , you may want to know which metadata table stores the IS rule related information or rule binding information ; create a rule and bind it to a table column.Then run the above query/analyze the profiler, which would list the tables which are late updated / scanned.

In one of my recent project , I have used this querying approach extensively with a fair amount of success to build the custom metadata reports.