That is something that is really bothering me.
There seems so be so much useful metadata stored and available in SQL Server, but i dont even know where to start or what tutorial/guide/books to look for.
The only thing i am using is the INFORMATION_SCHEMA from time to time, but that's it.
It's worth learning the system tables in SQL Server and how they hang together. All RDBMS have similar tables. This is Dr Codds rule 4. The database maintains its own catalogue
- RedShift/PostGres have pg_catalog tables
- Vertica has v_catalog tables
- MySQL relies on INFORMATION_SCHEMA and the actual MySQL database
- Google BigQuery has some (very expensive) support for INFORMATION_SCHEMA
- SQL Server has sys tables and views as well as INFORMATION_SCHEMA
One thing I would advise people who build ETL pipelines is to make it data driven. Model the data mapping specs and get them imported into the DB so you have queryable metadata. One of the common things that I get asked is "what is the impact if we change 'x', what will it break"? Actually having the data lineage represented by a mapping spec as queryable data makes that a trivial task.
Note that Redgate SQLDoc scraped and publishes structural metadata and descriptions from SQL Server databases.