Geeks With Blogs

News Google

Nick Harrison Blog<Nick>.Next()

SQL Tips for Learning a new Database


The hardest part of writing SQL statements is in understanding the database that you are working with. The raw mechanics of SQL for the most part are fairly straight forward. Often times really complex SQL statements point to business logic showing up where it doesn’t belong.

The hard part about learning a database is the lack of consistency and documentation. Data diagrams are often of little help. Diagrams are good for an overview and to get a list of tables, etc, but when the database has hundreds of tables with some tables having dozens of columns; the diagram can be a little overwhelming. Sometimes, a data diagram may not be helpful because all of the relationships are not actually defined. Poor design or data integrity problems often mean that foreign keys are implied but not enforced by the database. A data diagram will offer little help in finding such relationships, but you may still be able to ferret out such relationships through the system tables.

Every database includes a data dictionary that the database server uses to keep everything straight. We can use this data dictionary to our own benefit.

Instead of simply viewing an alphabetical list of tables, we can view all of the tables that include a given column or a given combination of columns or include these two columns but not a third column. We can easily get a list of all of the tables that include any columns that are of a particular data type or have more than a given number of columns or less than a given number of columns. There is a lot of potential here.

For SQL Server, the main tables that you will be interested in are in the information_shcmea views. These views exist in every database, but they are only visible in the master database. To make most of this magic work, we will really be interested in just a couple of views. Tables and Columns.

Information_Schema.Tables will give us some details about all of the tables in a given database.

Information_Schema.Columns will give us some details about of the columns in a given table.

With these two views, we can write queries such as:

Select * from information_Schema.tables where table_name like ‘%blah%’

Select * from information_Schema.columns where column_name = ‘Member_ID’

Get a list of tables that include a Member_ID column, a FirstName column and any DateTime column and at least 20 columns total.

Select * from information_schema.tables where table_name in (

Select table_name from information_schema.columns where column_name = ‘Member_ID’)

And table_name in (

Select table_name from information_schema.columns where column_name = ‘FirstName’)

And table_name in (

Select table_name from information_schema.columns where datatype = ‘DateTime’)

And table_name in (

select table_name from (select table_name, count (1)

From information_schema.columns

Group by table_name

Having count(1) > 20)


As you can see the possibilities are limited only by your imagination.

Oracle provides the same functionality. Actually every relational database must. For Oracle, the view in question would be all_tables and all_tab_columns.

Posted on Saturday, September 20, 2008 10:11 PM SQL Tricks | Back to top

Comments on this post: SQL Tips for Learning a New Database

comments powered by Disqus

Copyright © Nick Harrison | Powered by: