View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
There is no shortage of system views and DMVs to give us information about SQL Server databases. One of my favorites is the series of Information Schema views. The Information Schema views are easy to use and cover the metadata of the tables and other objects in the database.
Each Information Schema view covers one type of object or property such as foreign key constraints and permissions within a database. The view I use most often covers the columns, INFORMATION_SCHEMA.COLUMNS. Here is a list of the views:
The check constraints
The columns using a user defined data type
The columns with specific permissions defined
The columns, including information about data types
The constraints along with the columns affected
The constraints along with the tables affected
The user defined data types with rules defined
The user defined data types
The primary keys
The parameters of user defined functions or stored procedures
The foreign key constraints
The user defined functions and stored procedures
The columns returned by table-valued functions
The tables with constraints defined
The permissions granted to the tables
The columns used in view definitions
The tables used in view definitions
To use one of the Information Schema views, query it just like a table:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
As a consultant, I am often looking at databases I have never seen before, sometimes with documentation and sometimes not. I use the INFORMATION_SCHEMA.COLUMNS view to help me find tables containing a particular column and more.
The Information Schema views can be another tool to help you work more efficiently!