There are many instances where I like to get details about tables, views and the fields contained within a given database. A simple way to get at this information is querying a database’s INFORMATION_SCHEMA views. The following post will give a couple examples of how to work with these valuable objects.
From MSDN: An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.
Let’s get started. I have the following database, with these tables and views:
If I wanted to run a query to list the tables and views, I can use the INFORMATION_SCHEMA.TABLES view. Notice in the FROM clause, you have to specify the Database first, then INFORMATION_SCHEMA and finally the view you are looking to pull information from:
Database: FantasyStats
Method: INFORMATION_SCHEMA
View: TABLES
|
1 2 3 4 |
SELECT * FROM FantasyStats.INFORMATION_SCHEMA.TABLES |
Now, if I wanted to run a query to list out all the fields contained in each table/view, I can use the INFORMATION_SCHEMA.COLUMNS view:
|
1 2 3 4 |
SELECT * FROM FantasyStats.INFORMATION_SCHEMA.COLUMNS |
The INFORMATION_SCHEMA.COLUMNS view returns a lot of detail. If you want to look at a single table with a few important informational attributes:
|
1 2 3 4 5 6 7 8 9 |
SELECT TABLE_CATALOG , TABLE_CATALOG , COLUMN_NAME , DATA_TYPE FROM FantasyStats.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'PlayerName' |
If you wanted to list out all the views in the database, and see the actual view definition (code behind the view):
|
1 2 3 4 5 6 7 |
SELECT TABLE_CATALOG , TABLE_NAME , VIEW_DEFINITION FROM FantasyStats.INFORMATION_SCHEMA.VIEWS |
As you can see, the INFORMATION_SCHEMA views contain a lot of valuable metadata about tables and views in each database. I find these to be very handy when I need to evaluate or use this information in analysis and research purposes.
If you would like to post any additional examples, please add them to the comments below.
Additional Content
Check out more examples by visiting my Home Page
Here you will find topics covering Qlikview SQL Server Excel VBA