Link to home
Start Free TrialLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

asked on

List tables that have a certain prefix

Hello Expert,

Need to identify all tables in database
that begin with prefix  'D_'.

In PL/SQL (Oracle 12c) have tried

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE
WHERE TABLE_NAME LIKE 'D_%'

SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE name = 'd_%';

Both of these return
'Invalid table name'

Think these may be for SQL Server

Tried

SELECT TABLE_NAME
FROM ccm.user_tables
WHERE TABLE_NAME LIKE 'D_%'

This returns 'Table or view does not exist'

Thanks.

Allen in Dallas
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

SELECT *
FROM DBA_TABLES
WHERE TABLE_NAME LIKE 'D~_%' ESCAPE '~'
Avatar of Allen Pitts

ASKER

Hello Scott,

Works like new car.

What is the function of the tildes in
WHERE TABLE_NAME LIKE 'D~_%' ESCAPE '~'
?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great explanation. Thanks.