jump to navigation

Search Table structure from Data dictionaries: Oracle vs SQL Server vs DB2 vs Informix vs MYSQL vs SYBASE March 4, 2010

Posted by sendtoshailesh in Uncategorized.
Tags:
trackback

This is next to one of my previous blog about search tables…………..

This blogs explains how to dig the table structure, in case, if you do not want to use DESCRIBE SQL*PLUS command. Moreover, This is more useful when table structure need to fetch up programmatically. This blogs is comparative study across different database product.

you need to replace red marked search keyword from the codes with identified keyword that needs to be searched.

Oracle:

select owner schema,table_name,Column Name,DATA_TYPE,DATA_LENGTH

from all_tab_columns

where column_name like ‘%’||upper(‘search_keyword‘)||’%’

or table_name like ‘%’||upper(‘search_keyword’)||’%’;

DB2:

select TABSCHEMA schema,TABNAME Table_name,COLNAME Column_Name,TYPENAME Data_type,LENGTH DATA_LENGTH

from SYSCAT.COLUMNS

where COLNAME like ‘%’||upper(‘search_keyword’)||’%’

or TABNAME like ‘%’||upper(‘search_keyword’)||’%’;

MS-SQL Server:

SELECT table_name,column_name,schema_name,type_name,max_length, precision,scale from (

SELECT c.name AS column_name

,c.column_id

,SCHEMA_NAME(t.schema_id) AS schema_name

,t.name AS type_name

,t.is_user_defined

,t.is_assembly_type

,c.max_length

,c.precision

,c.scale

,o.name table_name

FROM sys.columns AS c

JOIN sys.types AS t ON c.user_type_id=t.user_type_id

JOIN sys.objects AS o on c.object_id = o.object_id

WHERE o.type_desc LIKE ‘%TABLE%’) all_tab_columns

WHERE all_tab_columns.column_name like ‘%’+upper(‘Search_Keyword‘)+’%’;

Informix:

select b.owner schema_name,b.tabname table_name,a.colname Column_Name,

decode(coltype,0,’CHAR’,1,’SMALLINT’,2,’INTEGER’,3,’FLOAT’,4,’SMALLFLOAT’,5,’DECIMAL’,6,’SERIAL’,7,’DATE’,

8,’MONEY’,9,’NULL’,10,’DATETIME’,11,’BYTE’,12,’TEXT’,13,’VARCHAR’,14,’INTERVAL’,15,’NCHAR’,16,’NVARCHAR’,

17,’INT8′,18,’SERIAL8′,19,’SET’,20,’MULTISET’,21,’LIST’,22,’Unnamed ROW’,40,’Variable-length opaque type’,

4118,’Named ROW’,to_char(coltype)) DATA_TYPE,

collength DATA_LENGTH

from syscolumns a,systables b

where a.tabid=b.tabid and b.tabtype=’T’

and upper(colname) like ‘%’||upper(‘Search_Keyword‘)||’%’;

MYSQL:

select table_schema schema_name,table_name,Column_Name,DATA_TYPE

from INFORMATION_SCHEMA.COLUMNS

where column_name like concat(concat(‘%’,upper(‘search_keyword’)),’%’)

or table_name like concat(concat(‘%’,upper(‘search_keyword’)),’%’);

SYBASE:

select b.creator schema,b.table_name,a.Column_Name,c.domain_name DATA_TYPE,width DATA_LENGTH

from syscolumn a,systable b,SYSDOMAIN c

where b.table_type not like ‘%VIEW%’

and a.table_id=b.table_id

and a.domain_id=c.domain_id

and column_name like ‘%’||upper(‘Search_Keyword‘)||’%’;

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: