Home » SQL & PL/SQL » SQL & PL/SQL » need help with script
need help with script [message #202204] Wed, 08 November 2006 13:52 Go to next message
mfreeze
Messages: 5
Registered: September 2005
Location: Pennsylvania
Junior Member
SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';' FROM USER_TABLES
WHERE (TABLE_NAME LIKE '%CODE'
OR TABLE_NAME LIKE '%MAP'
OR TABLE_NAME LIKE '%MAPPING'
OR TABLE_NAME LIKE '%XREF'
OR TABLE_NAME LIKE 'DMC%'
OR TABLE_NAME = 'ROW_STATUS'
AND NOT = 'DM_%'
)
/

I have two table groups called DMC_XXXXXX_CODE and DM_XXXXXX_CODE. The DMC are code tables and need to get truncated and do. The DM tables are not code tables and should not get truncated but do. Someone here screwed up in the naming conventions and it cannot be fixed by renaming the tables correctly because to much programming surrounds the tables. How do I get this scripts to not truncate the DM_XXXXXX_Code tables?Thanks for your assistance.
Re: need help with script [message #202208 is a reply to message #202204] Wed, 08 November 2006 14:08 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SELECT 'TRUNCATE TABLE ' || TABLE_NAME || ';' FROM USER_TABLES
WHERE (TABLE_NAME LIKE '%CODE'
OR TABLE_NAME LIKE '%MAP'
OR TABLE_NAME LIKE '%MAPPING'
OR TABLE_NAME LIKE '%XREF'
OR TABLE_NAME LIKE 'DMC%'
OR TABLE_NAME = 'ROW_STATUS')
AND TABLE_NAME NOT LIKE = 'DM\_%' escape '\'
/


Edited to make it a little cleaner.

[Updated on: Wed, 08 November 2006 14:27]

Report message to a moderator

Previous Topic: A question about roles
Next Topic: Dynamically naming and declaring variables
Goto Forum:
  


Current Time: Sat Dec 03 03:39:37 CST 2016

Total time taken to generate the page: 0.08036 seconds