In SQL Server there is two schemas for metadata:
I would always try to use the
Information_schema views over querying the
sys schema directly.
The Views are ISO compliant so in theory you should be able to easily migrate any queries across different RDBMS.
However, there have been some cases where the information that I need is just not available in a view.
I've provided some links with further information on the views and querying a SQL Server Catalog.