Database Metadata

To dynamically generate many data-driven applications, you need to retrieve data definitions from an RDBMS. If your application is table driven—that is, if it generates output for a given table—you can accomplish this by retrieving table and column information from your database's metadata tables. Given that SQL Server and Oracle are the most popular databases on the market, this section examines these RDBMSs in detail. This serves to illustrate that these techniques are RDBMS independent and that using them in something other than SQL Server isn't difficult.

SQL Server

SQL Server stores its metadata in a series of system tables that can be joined with one another to retrieve information about tables and columns. Before you go too far down this road, be aware that you can obtain the same information from INFORMATION_SCHEMA views, which provide a much simpler access method.

System tables such as sys.tables and sys.columns return the data that their names suggest. However, you likely need to JOIN them to achieve the results you're looking for. Suppose you want a list of the table names, column names, and data types of every column in a given table. You can obtain this information by creating a JOIN between the sys tables like this: SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, c.name AS ColumnName, y.name as type, c.max_length FROM sys.columns c

LEFT OUTER JOIN sys.tables t ON c.object_id = t.object_id

LEFT OUTER JOIN sys.types y ON c.system_type_id = y.system_type_id

ORDER BY TableName, c.column_id

This SQL produces the output shown in Figure 1-1.

SchemaName

TableName

ColumnName

type I

maxjength |

1

i dbo

Categories

CategorylD

int

4

2_

dbo

Categories

Category Name

nvarchar

30

J_

dbo

Categories

CategoryName

sysname

30

_4_

dbo

Categories

Description

ntext

16

5

dbo

Categories

Picture

image

1G

_6_

dbo

CustomerCustomerD emo

CustomerlD

nchar

10

7

dbo

CustomerCustomerD emo

CustomerTypelD

nchar

20

8

dbo

CustomerD emographics

CustomerTypelD

nchar

20

_9_

dbo

CustomerD emographics

CustomerD esc

ntext

16

10

dbo

Customers

CustomerlD

nchar

10

11

dbo

Customers

CompanyName

nvarchar

80

12

dbo

Customers

CompanyName

sysname

80

Figure 1 -1. SQL Server table and column metadata

Figure 1 -1. SQL Server table and column metadata

Because the INFORMATION_SCHEMA views already perform these JOINs for you, you can obtain the same information like this:

SELECT TABLE_SCHEMA AS SchemaName, TABLE_NAME AS TableName,

COLUMN_NAME AS ColumnName, DATA_TYPE as type,

CHARACTER_MAXIMUM_LENGTH AS max_length

FROM INFORMATION_SCHEMA.COLUMNS

ORDER BY TABLE_NAME, ORDINAL_POSITION

This SQL produces the output shown in Figure 1-2.

E3 Results I [j Messages |

TABLE SCHEMA

TABLE NAME

COLUMN NAME

DATA.TYPE

CHARACTER MAXIMUM LENGTH

1

! dbo

Alphabetical list of products

ProductID

int

NULL

2

dbo

Alphabetical list of products

ProductName

nvarchar

40

3

dbo

Alphabetical list of products

SupplierlD

int

NULL

4

dbo

Alphabetical list of products

CategorylD

int

NULL

5

dbo

Alphabetical list of products

QuantityPerUnit

nvarchar

20

6

dbo

Alphabetical list of products

UnitPrice

money

NULL

7

dbo

Alphabetical list of products

UnitslnStock

smallint

NULL

8

dbo

Alphabetical list of products

UnitsOnOrder

smallint

NULL

9

dbo

Alphabetical list of products

ReorderLevel

smallint

NULL

10

dbo

Alphabetical list of products

Discontinued

bit

NULL

11

dbo

Alphabetical list of products

CategoryName

nvarchar

15

12

dbo

Categories

CategorylD

int

NULL

zi

Figure 1 -2. SQL Server table and column metadata from the INFORMA TION_SCHEMA view

As you can see, taking advantage of the INFORMATION_SCHEMA views is much easier. It does, however, have one drawback. INFORMATION_SCHEMA shows the maximum length values only for string data types. Other values show as NULL. This shouldn't be a burden, because the other data types have lengths that are specific to their data type and can't be changed.

WORKING WITH TABLE JOINS

If you need to generate output for the results of a join between many tables, create a table structure that holds the structure of the join results like this example from Northwind:

SELECT TOP 0 o.OrderlD, o.OrderDate, c.CompanyName, e.LastName, e.FirstName INTO MyStructure FROM Orders o

LEFT OUTER JOIN Customers c ON o.CustomerlD = c.CustomerlD LEFT OUTER JOIN Employees e ON o.EmployeelD = e.EmployeelD WHERE e.EmployeelD = 5

You can then generate your code from the MyStructure table and drop it when you're done.

Oracle

Like SQL Server, Oracle has its own metadata tables, which are made accessible by a series of views whose names begin with all_. You can see a list of these tables and views by running this command:

SELECT table_name, comments FROM dictionary ORDER BY table_name;

The Oracle Dictionary table contains the list of all tables and views. Figure 1-3 shows the list of the main metadata tables along with their descriptions.

TABLE.NAME

COMMENTS

ALL_TAB_COLS

Columns of user's tables, views and dusters

ALL_TAB_COLUMNS

Columns of user's tables, views and clusters

ALL_TAB_COL_STATISTICS

Columns of user's tables, views and clusters

ALL_TAB_COMMENTS

Comments on tables and views accessible to the user

ALL_TAB_HISTGRM_PENDING_ST^ Pending statistics of tables, partitions, and subpartitions

ALL_TAB_HISTOGRAMS

Histograms on columns of all tables visible to user

»

ALL_TAB_MODIFICATIONS

Information regarding modifications to tables

ALL_TAB_PARTITIONS

ALL_TAB_PE N D1N G_STATS

Pending statistics of tables, partitions, and subpartitions

|lALL_TAB_PRIVS

0 Grants on objects forwhich the user is the grantor, grantee, owner.l or an enabled role or PUBLIC is the grantee

s

l<

- -»1- - II

>i

Figure 1-3. Oracle table metadata

Figure 1-3. Oracle table metadata

The information for the various tables is stored in the all_tables view. You can extract the names and row counts of the sample employee tables (Oracle's equivalent of Northwind) belonging to the user SCOTT using this SQL:

SELECT table_name, num_rows FROM all_tables WHERE owner = 'SCOTT' ORDER BY table_name;

The results of this query are shown in Figure 1-4.

Figure 1 -4. List of Oracle tables

If you're generating code, you most likely require the details about the various columns of a table. This example returns the column name, data type, length, precision, and scale for the columns in the EMP table:

SELECT column_name, data_type, data_length, data_precision, data_scale FROM all_tab_columns WHERE table_name = 'EMP';

The output of this command is shown in Figure 1-5.

columnname

data_type

data_length data_precision data_scale

empno

number

22 A 0

ename

varchar2

10

job

varchar2

9

mgr

number

22 A 0

hiredate

date

7

Figure 1 -5. Oracle column metadata

To extract information about constraints like primary keys and referential integrity rules, you can join the all_constraints and all_cons_columns tables. This SQL returns the output shown in Figure 1-6:

SELECT cc.table_name, cc.column_name, c.constraint_type FROM all_constraints c

INNER JOIN all_cons_columns cc ON c.constraint_name = cc.constraint_name

WHERE cc.owner = 'SCOTT'

ORDER BY cc.table_name, cc.position;

|<&|table_name

column_name

co n strai nt_type

a

D

dept

deptno

p

J

emp

deptno

r

:

emp

empno

p

Figure 1-6 Oracle constraint metadata

You can perform the same operation for SQL Server via this SQL:

SELECT OBJECT_NAME(parent_object_id) AS TableName, OBJECT_NAME(OBJECT_ID) AS ConstraintName, type_desc AS ConstraintType FROM sys.objects

WHERE type_desc LIKE '%CONSTRAINT'

0 0

Post a comment