jump to navigation

Search Tables from Data dictionaries: Oracle vs SQL Server vs DB2 vs Informix vs MYSQL vs SYBASE March 3, 2010

Posted by sendtoshailesh in Uncategorized.
trackback

Querying Data dictionary is tricky and tweaking. The more you know the data dictionaries the more you can dig out the database. In this regards, I am presenting how to search the the tables across different database product. It is useful to know the structure of data dictionaries across different DB products.

Code-part marked as red – This need to replace with search keyword identifier wherever SQL code is incorporated.

Oracle:

select owner schema,table_name

from all_tables

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

SQL Server:

SELECT table_name,schema_name,table_type from (

SELECT name AS table_name ,SCHEMA_NAME(schema_id) AS schema_name, type_desc table_type

FROM sys.objects

WHERE type_desc LIKE ‘%TABLE%’) all_tables

Where all_tables.table_name like ‘%’+upper(‘search_keyword‘)+’%’;

IBM DB2:

select TABSCHEMA schema_name,TABNAME

from syscat.tables

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

Informix:

select owner schema_name,tabname table_name

from systables

where tabtype=’T’ and upper(tabname) like ‘%’||upper(‘Search_Keyword‘)||’%’;

MYSQL:

select table_schema schema_name,table_name

from INFORMATION_SCHEMA.TABLES

where table_type like ‘%TABLE%’ and table_name like concat(concat(‘%’,upper(‘search_keyword‘)),’%’);

SYBASE:

select creator schema,table_name

from systable

where table_type not like ‘%VIEW%’

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

Advertisements

Comments»

1. Sergiy - March 6, 2014

Thank you! I works with Sybase to Oracle migration. This help me 🙂
I add your blog to my “Favorites”


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: