Whenever you come across a new database, depending on the size, it can be a bit intimidating trying to get familiar.  There could be any number of tables and from there an unknown number of views, procedures, functions, etc. that relate to each of those.

To get a quick look at what is going on in a database, you can use the INFORMATION_SCHEMA views which provide metadata about the objects in each database.  If you are unfamiliar with the INFORMATION_SCHEMA, it is an ANSI-standard set of views to provide metadata.

I will not cover all views in the INFORMATION_SCHEMA in this article but I am going to cover a few of the basics that you can use to get started.  All examples in this article are run in SQL Server in the AdventureWorks2017 database.

When looking at a new database, the first thing you will want to know is what tables there are.  You can see this by looking at INFORMATION_SCHEMA.TABELS

select * from INFORMATION_SCHEMA.TABLES

This is going to return a row for each table or view the current user can access with four columns: 

TABLE_CATALOG – which is the database name

TABLE_SCHEMA – the schema the table belongs to

TABLE_NAME – name of the table

TABLE_TYPE – this can either be VIEW or BASE TABLE

For me, this view is most useful in seeing the number of tables you have as well as seeing which tables are in which schema, assuming you have a database with multiple schemas. While you are getting to know a database, you can use this view to generate queries to get a general idea of what is in each table.

select 'select top 1 * from ' + table_schema + '.' + table_name from INFORMATION_SCHEMA.TABLES

You can then copy the results into a new query editor window and run them to get one record from each table (NOTE: you will not want to run all queries at once if you have more than a couple of tables in your database).

Once you have a general idea of what tables exist and you have taken a quick look at what data is in them, depending on what role you have at the company, your next logical step might be to wonder what views are available to you for querying.  That is where we come to the next view I want to cover, INFORMATION_SCHEMA.VIEWS

This metadata view will give you similar information to the TABLES view with a few extra columns.  The one I find most useful is the VIEW_DEFINITION column which will give you the definition text for the view.  You can use this if you want to find a view that has something in the query you are looking for such as a WINDOW FUNCTION or a specific table name.  For example if we want to find any views that reference the [HumanResources].[Employee] table we can run the following:

select * from INFORMATION_SCHEMA.VIEWS where VIEW_DEFINITION like '%[HumanResources].[Employee]%'

If you are looking for views that select from a specific table such as in the above example, rather than something more specific within the query, you can also use the view INFORMATION_SCHEMA.VIEW_TABLE_USAGE.  This is going to return a row for each table in the database that is used in a view.

select * from INFORMATION_SCHEMA.VIEW_TABLE_USAGE where TABLE_SCHEMA = 'HumanResources' and TABLE_NAME ='Employee'

If you want to know if there is a specific column being used in views there is another view called INFORMATION_SCHEMA.VIEW_COLUMN_USAGE that will give you a row for every column used in a view.

select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

The final view that I will cover in this article is INFORMATION_SCHEMA.ROUTINES.  This view returns a row for each stored procedure and function that the current user has access to.

select * from INFORMATION_SCHEMA.ROUTINES

The column I find most useful in this view is the ROUTINE_DEFINITION which contains the definition text of the stored procedure or function you are looking at.  This can be used similarly to the how I described using the VIEW_DEFINITION column earlier.  An example would be if you want to find any stored procedure or function that is using a cursor.

select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like '%cursor%'

This returns no results in the AdventureWorks2017 Database but you can see how it would be useful.

As you can see, these metadata views can be quite useful when getting to know a database or even if you are extremely familiar with a database but you need to change a table definition and want to see what is going to be affected.  Being familiar with the INFORMATION_SCHEMA is a great tool for anyone to have when working with databases at every level of an organization.