As far as I know in Microsoft SQL Server it is possible to use CURSOR to "scan" database and get all tables/entries where specified string is mentioned. Can I do this in Oracle 11g DB? Any caveats/limits for such scenario?

  • Are you looking for cases where a string exists in a column name? In a table name? Or in some row somewhere in the table? – Justin Cave Feb 24 '14 at 15:24
  • In the table values not in column/table names – Mikhail Feb 24 '14 at 15:36

I'm hard-pressed to imagine how you'd accomplish that with a single cursor in SQL Server.

The same question has been asked and answered on StackOverflow. If you want to get really tricky, you could also use an XML Query to search across objects with a single query.

In either case, the result is going to be insanely slow since you're full scanning every table at least if not once per character column.

| improve this answer | |
  • I don't care about speed particularly as I have non production copy of database against which I can run this query, the only thing I need is to be sure that I know all locations in database where particular value/string is present - so I'm trying to find the best way to get this information – Mikhail Feb 25 '14 at 7:42
  • @Mikhail - The answer I gave on StackOverflow should work. It'll be slow as dirt. But it will work. – Justin Cave Feb 25 '14 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.