Every developer, database or not, has had those moments when someone wants the data dictionary. Instead of the painful task of hand typing it out here are a couple data dictionary scripts to get you started.
SQL Server Data Dictionary Script
select s.name as SchemaName,
t.name as TableName,
c.name as ColumnName,
typ.name as DataType,
c.max_length as MaxLength,
c.precision as Precision,
c.scale as Scale,
c.is_nullable as IsNullable,
c.is_identity as IsIdentity,
ISNULL(i.is_primary_key, 0) IsPrimaryKey,
CASE WHEN fkc.constraint_column_id is null THEN 0 ELSE 1 END as IsForeignKey,
o.name as ReferencyTable,
CONVERT(VARCHAR(MAX), ep.value) as Description
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.columns c on t.object_id = c.object_id
JOIN sys.types typ ON c.user_type_id = typ.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
LEFT OUTER JOIN sys.foreign_key_columns fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN sys.objects as o on fkc.referenced_object_id = o.object_id
LEFT OUTER JOIN sys.extended_properties as ep on c.object_id = ep.major_id and c.column_id = ep.minor_id
order by SchemaName, TableName, IsPrimaryKey Desc, IsForeignKey Desc, ColumnName
Need more detail? Microsoft online documentation can help.
Oracle Data Dictionary Script
select utc.table_name,
utc.column_name,
utc.data_type,
utc.data_length,
utc.data_precision,
utc.nullable,
decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(utc.low_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(utc.low_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(utc.low_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(utc.low_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(utc.low_value))
,'DATE',to_char(1780+to_number(substr(utc.low_value,1,2),'XX')
+to_number(substr(utc.low_value,3,2),'XX'))||'-'
||to_number(substr(utc.low_value,5,2),'XX')||'-'
||to_number(substr(utc.low_value,7,2),'XX')||' '
||(to_number(substr(utc.low_value,9,2),'XX')-1)||':'
||(to_number(substr(utc.low_value,11,2),'XX')-1)||':'
||(to_number(substr(utc.low_value,13,2),'XX')-1)
, utc.low_value) as LOW_VALUE,
--utc.low_value,
decode(data_type
,'NUMBER' ,to_char(utl_raw.cast_to_number(utc.high_value))
,'VARCHAR2' ,to_char(utl_raw.cast_to_varchar2(utc.high_value))
,'NVARCHAR2' ,to_char(utl_raw.cast_to_nvarchar2(utc.high_value))
,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(utc.high_value))
,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(utc.high_value))
,'DATE',to_char(1780+to_number(substr(utc.high_value,1,2),'XX')
+to_number(substr(utc.high_value,3,2),'XX'))||'-'
||to_number(substr(utc.high_value,5,2),'XX')||'-'
||to_number(substr(utc.high_value,7,2),'XX')||' '
||(to_number(substr(utc.high_value,9,2),'XX')-1)||':'
||(to_number(substr(utc.high_value,11,2),'XX')-1)||':'
||(to_number(substr(utc.high_value,13,2),'XX')-1)
, utc.high_value) as HIGH_VALUE,
--utc.high_value,
utc.num_nulls,
utc.identity_column,
case uc.constraint_type when 'P' then 'Primary Key' when 'R' then 'Foreign Key' else null end as KEY_TYPE,
ucr.table_name as REFERENCE_TABLE,
uc.r_constraint_name as REFERENCE_COLUMN
from user_tab_cols utc
left outer join user_cons_columns ucc on utc.table_name = ucc.table_name and utc.column_name = ucc.column_name
left outer join user_constraints uc on ucc.constraint_name = uc.constraint_name
left outer join user_constraints ucr on uc.r_constraint_name = ucr.constraint_name
order by utc.table_name, utc.column_name;
If you want to take Oracle a step further, check out Oracle’s online documentation.
1 Comment
Comments are closed.
Oracle sucks 😉