Data Blueprint

Announcing Partnership Between Dataversity and Data Blueprint‏

2011-06-28T13:25:00.000-07:00

Announcing Partnership Between Dataversity and Data Blueprint‏

System Tables 101

2011-03-24T07:22:00.000-07:00

At Data Blueprint, we always have new data sets to do work on. These can sometimes be massive, which can make navigating them difficult. During the Data Discovery phase of a new project, it's vital to know where individual attributes proliferate. Fortunately, most SQL-based database systems make this easy by storing metadata in several useful System Tables.

This post is designed as a brief introduction to System Tables,

Here are a few useful queries to get you started (examples are for SQL SERVER 2008):

Get The Name of All Objects

SELECT name
FROM sys.all_objects

Get The metadata for all tables

SELECT *
FROM sys.all_objects
WHERE TYPE_DESC ='SYSTEM_TABLE'

Both of those are fairly straight forward, and the wide variety of uses for this information should quickly become apparent.

Here is one more example with a slightly more specific purpose:

Find all tables which contain a column with a name like "PRICE" or "PRC"

SELECT col.name as 'COLUMN', obj.name as 'TABLE' FROM syscolumns col
  LEFT OUTER JOIN sys.all_objects obj   
  ON col.id = obj.object_id
 where col.name like '%PRICE%'
  OR col.name like '%PRC%'

These examples only scratch the surface of what information can be extracted from the System Tables, but hopefully this post has been enlightening for anyone unfamiliar with the concept.

Documentation Links

SQL Server System Catalog
System Tables in POSTGRESQL
List of System Tables in Oracle
DB2 System Catalog

Hello World!

2011-02-17T07:41:00.000-08:00

Data Blueprint is a Richmond-based data management and IT consulting firm that was founded by Dr. Peter Aiken in 1999. With origins at Virginia Commonwealth University, we are focused on improving organizational data and data management practices.

Our core services include:

We are dedicated to helping organizations gain more value from their data assets by utilizing industry leading data management methodologies that improve data quality, reduce implementation costs and decrease time-to-market for strategic IT projects.

Our team consists of experienced IT professionals who have received numerous awards and recognitions, including the 2010 International Stevens Award, an invitation to participate on the Defense Logistics Information Research (DLIR) Program Technical Solutions Council (TSC), a nomination as a finalist for the Greater Richmond Technology Council IT Builder's Award, DAMA International Community and Individual Service Awards, as well as a nomination for the Virginia Museum of Fine Arts MUSE Award.

Our award-winning, internationally recognized approaches to data architecture and engineering have resulted in centuries of time and tens of millions of dollars in savings to our clients.

To learn more about our results, please take a look at our success stories.