Loading ...
Sorry, an error occurred while loading the content.

Re: [toad] How do I list all tables that have a particular column

Expand Messages
  • Geoffroy Braem
    select table_name from sys.dba_tab_columns where column_name like %string% ; This assumes that you have select privilege on the sys views. Geoffroy
    Message 1 of 13 , Nov 30, 2005
    • 0 Attachment
      select table_name from sys.dba_tab_columns where column_name like
      '%string%';

      This assumes that you have select privilege on the sys views.

      Geoffroy




      Rick wrote:
      > How can I use Toad to get a list of all tables that have a column that
      > has a particular string in it. For example, if I wanted a list of all
      > tables that have a column containing the text "user", how would I do
      > that?
      >
      > Thanks in advance.
      >
      >
      >
      >
      >
      >
      >
      > Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html
      >
      > Do you know about the other TOAD Yahoo Message Boards? Go here to find out more - http://www.toadsoft.com/maillist.htm
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
      >
      >
      >
    • Prall-Paul
      Go to Tools - Object Search. For your example use %user%. If you truly want All schemas / owners go to the top of the list and select *ALL USERS. Otherwise
      Message 2 of 13 , Nov 30, 2005
      • 0 Attachment
        RE: [toad] How do I list all tables that have a particular column

        Go to Tools -> Object Search. For your example use %user%.

        If you truly want All schemas / owners go to the top of the list and select *ALL USERS. Otherwise just select the schema you are specifically interested in.

        Watch the Options Box. This can impact search time. Select only the options you are truly interested in.

        Paul

        -----Original Message-----
        From: Rick [mailto:elvis_q@...]
        Sent: Wednesday, November 30, 2005 2:37 PM
        To: toad@yahoogroups.com
        Subject: [toad] How do I list all tables that have a particular column


        How can I use Toad to get a list of all tables that have a column that
        has a particular string in it.  For example, if I wanted a list of all
        tables that have a column containing the text "user", how would I do
        that?

        Thanks in advance.







        Questions?  Try the on-line FAQ: http://www.toadsoft.com/faq2.html

        Do you know about the other TOAD Yahoo Message Boards?  Go here to find out more - http://www.toadsoft.com/maillist.htm

        Yahoo! Groups Links

        <*> To visit your group on the web, go to:
            http://groups.yahoo.com/group/toad/

        <*> To unsubscribe from this group, send an email to:
            toad-unsubscribe@yahoogroups.com

        <*> Your use of Yahoo! Groups is subject to:
            http://docs.yahoo.com/info/terms/
         



        THIS E-MAIL MESSAGE IS INTENDED FOR THE NAMED RECIPIENT(S) ABOVE.  IT MAY CONTAIN CONFIDENTIAL INFORMATION THAT IS PRIVILEGED OR THAT CONSTITUTES ATTORNEY WORK PRODUCT.  IF YOU ARE NOT THE INTENDED RECIPIENT, YOU ARE HEREBY NOTIFIED THAT ANY DISSEMINATION, DISTRIBUTION OR COPYING OF THIS E-MAIL AND ANY ATTACHMENT(S) IS STRICTLY PROHIBITED.  IF YOU HAVE RECEIVED THIS E-MAIL IN ERROR, PLEASE IMMEDIATELY NOTIFY THE SENDER BY REPLYING TO THIS E-MAIL AND DELETE THE MESSAGE AND ANY ATTACHMENT(S) FROM YOUR SYSTEM.  THANK YOU.

      • Ed Klinger
        In the Schema Browser click on the Filter icon on the tables tab. Their you will see a filter on column name. I don t know what version you are on but I know
        Message 3 of 13 , Nov 30, 2005
        • 0 Attachment
          In the Schema Browser click on the Filter icon on the tables tab. Their you
          will see a filter on column name. I don't know what version you are on but
          I know for sure in 8.0 and above that option existed.

          Otherwise you can do it the old fashion way in the SQL Editor

          SELECT t.table_name
          FROM user_tables t, user_tab_columns c
          WHERE t.table_name = c.table_name
          AND c.column_name LIKE '%USER%'

          Ed
          [TeamT]

          -----Original Message-----
          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Rick
          Sent: Wednesday, November 30, 2005 2:37 PM
          To: toad@yahoogroups.com
          Subject: [toad] How do I list all tables that have a particular column

          How can I use Toad to get a list of all tables that have a column that has a
          particular string in it. For example, if I wanted a list of all tables that
          have a column containing the text "user", how would I do that?

          Thanks in advance.







          Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

          Do you know about the other TOAD Yahoo Message Boards? Go here to find out
          more - http://www.toadsoft.com/maillist.htm
          Yahoo! Groups Links








          **************************************************************************
          Notice of Confidentiality: This e-mail communication and the attachments hereto, if any, are intended solely
          for the information and use of the addressee(s) identified above and may contain information which is legally
          privileged and/or otherwise confidential. If a recipient of this e-mail communication is not an addressee (or an
          authorized representative of an addressee), such recipient is hereby advised that any review, disclosure,
          reproduction, re-transmission or other dissemination or use of this e-mail communication (or any information
          contained herein) is strictly prohibited. If you are not an addressee and have received this e-mail communication
          in error, please advise the sender of that circumstance either by reply e-mail or by telephone at (407) 384-8818
          immediately delete this e-mail communication from any computer and destroy all physical copies of same.

          Replies Filtered: Any incoming e-mail reply to this communication will be electronically filtered for "spam"
          and/or "viruses." That filtering process may result in such reply being quarantined (i.e., potentially not received
          at our site at all) and/or delayed in reaching us. For that reason, we cannot guarantee that we will receive your
          reply and/or that we will receive it in a timely manner. Accordingly, you should consider sending communications
          to us which are particularly important or time-sensitive by means other than e-mail.
          **************************************************************************
        • Jacobs, Jeffrey
          Or, in the Schema Browser, you can set the Filter options... ... From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Jesse, Rich Sent:
          Message 4 of 13 , Nov 30, 2005
          • 0 Attachment
            Or, in the Schema Browser, you can set the "Filter" options...

            -----Original Message-----
            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
            Jesse, Rich
            Sent: Wednesday, November 30, 2005 12:38 PM
            To: toad@yahoogroups.com
            Subject: RE: [toad] How do I list all tables that have a particular
            column

            Object Search is your friend! Tools->"Object Search"

            In the Options tab, uncheck everything but "Column Names" and make sure
            the dropdown says "Texr occurs anywhere" unless you want otherwise.
            Select the appropriate schema from the left hand side. Type "user"
            (sans quotes) in the "Search for" dropdown and click "Search".

            <ding> Search is done!

            HTH! Enjoy!

            Rich -- [TeamT]

            -----Original Message-----
            From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
            Rick
            Sent: Wednesday, November 30, 2005 1:37 PM
            To: toad@yahoogroups.com
            Subject: [toad] How do I list all tables that have a particular column


            How can I use Toad to get a list of all tables that have a column that
            has a particular string in it. For example, if I wanted a list of all
            tables that have a column containing the text "user", how would I do
            that?

            Thanks in advance.


            Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

            Do you know about the other TOAD Yahoo Message Boards? Go here to find
            out more - http://www.toadsoft.com/maillist.htm
            Yahoo! Groups Links
          • Erwin Rollauer
            In SB under schema SYS look at view DBA_TAB_COLUMNS Put in a filter such as COLUMN_NAME LIKE %USER% Make sure the text is capitalized Erwin Rollauer, ISR,
            Message 5 of 13 , Nov 30, 2005
            • 0 Attachment
              In SB
              under schema SYS
              look at view DBA_TAB_COLUMNS

              Put in a filter such as
              COLUMN_NAME LIKE '%USER%'

              Make sure the text is capitalized

              Erwin Rollauer, ISR, McGill, 514 398-5023 ex 00626

              -----Original Message-----
              From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
              Rick
              Sent: Wednesday, November 30, 2005 2:37 PM
              To: toad@yahoogroups.com
              Subject: [toad] How do I list all tables that have a particular column

              How can I use Toad to get a list of all tables that have a column that
              has a particular string in it. For example, if I wanted a list of all
              tables that have a column containing the text "user", how would I do
              that?

              Thanks in advance.







              Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

              Do you know about the other TOAD Yahoo Message Boards? Go here to find
              out more - http://www.toadsoft.com/maillist.htm
              Yahoo! Groups Links
            • Chuck
              ... There are several ways to do it but all involve dynamic SQL. Basically you re going to need to write one SQL that s going to generate one or more other
              Message 6 of 13 , Nov 30, 2005
              • 0 Attachment
                Rick wrote:

                >How can I use Toad to get a list of all tables that have a column that
                >has a particular string in it. For example, if I wanted a list of all
                >tables that have a column containing the text "user", how would I do
                >that?
                >
                >Thanks in advance.
                >

                There are several ways to do it but all involve dynamic SQL. Basically
                you're going to need to write one SQL that's going to generate one or
                more other SQL's, then run the generated one(s). I'd need more detail to
                give more specific help.

                Will the column name be the same in all the tables you want to check? Or
                are you trying say "give me any column of any name in any table that
                contains the value "user"?

                Can I ask what the point of this exercise is? In ten years as a DBA I
                don't recall ever encountering this problem before. That's not to say
                that it can't be done, but it's highly unusual for a relational database
                application.

                Chuck
              • Chuck
                ... There are several ways to do it but all involve dynamic SQL. Basically you re going to need to write one SQL that s going to generate one or more other
                Message 7 of 13 , Dec 1, 2005
                • 0 Attachment
                  Rick wrote:

                  >How can I use Toad to get a list of all tables that have a column that
                  >has a particular string in it. For example, if I wanted a list of all
                  >tables that have a column containing the text "user", how would I do
                  >that?
                  >
                  >Thanks in advance.
                  >

                  There are several ways to do it but all involve dynamic SQL. Basically
                  you're going to need to write one SQL that's going to generate one or
                  more other SQL's, then run the generated one(s). I'd need more detail to
                  give more specific help.

                  Will the column name be the same in all the tables you want to check? Or
                  are you trying say "give me any column of any name in any table that
                  contains the value "user"?

                  Can I ask what the point of this exercise is? In ten years as a DBA I
                  don't recall ever encountering this problem before. That's not to say
                  that it can't be done, but it's highly unusual for a relational database
                  application.

                  Chuck
                • mai tran
                  I use a really simple SQL to check: SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE %USER% ...
                  Message 8 of 13 , Dec 1, 2005
                  • 0 Attachment
                    I use a really simple SQL to check:

                    SELECT owner, table_name, column_name FROM
                    all_tab_columns WHERE column_name LIKE '%USER%'



                    --- Chuck <skilover@...> wrote:

                    > Rick wrote:
                    >
                    > >How can I use Toad to get a list of all tables that
                    > have a column that
                    > >has a particular string in it. For example, if I
                    > wanted a list of all
                    > >tables that have a column containing the text
                    > "user", how would I do
                    > >that?
                    > >
                    > >Thanks in advance.
                    > >
                    >
                    > There are several ways to do it but all involve
                    > dynamic SQL. Basically
                    > you're going to need to write one SQL that's going
                    > to generate one or
                    > more other SQL's, then run the generated one(s). I'd
                    > need more detail to
                    > give more specific help.
                    >
                    > Will the column name be the same in all the tables
                    > you want to check? Or
                    > are you trying say "give me any column of any name
                    > in any table that
                    > contains the value "user"?
                    >
                    > Can I ask what the point of this exercise is? In ten
                    > years as a DBA I
                    > don't recall ever encountering this problem before.
                    > That's not to say
                    > that it can't be done, but it's highly unusual for a
                    > relational database
                    > application.
                    >
                    > Chuck
                    >
                    >
                    >
                    >
                    >
                    > Questions? Try the on-line FAQ:
                    > http://www.toadsoft.com/faq2.html
                    >
                    > Do you know about the other TOAD Yahoo Message
                    > Boards? Go here to find out more -
                    > http://www.toadsoft.com/maillist.htm
                    > Yahoo! Groups Links
                    >
                    >
                    > toad-unsubscribe@yahoogroups.com
                    >
                    >
                    >
                    >
                    >




                    __________________________________________
                    Yahoo! DSL – Something to write home about.
                    Just $16.99/mo. or less.
                    dsl.yahoo.com



                    __________________________________________
                    Yahoo! DSL – Something to write home about.
                    Just $16.99/mo. or less.
                    dsl.yahoo.com
                  • pdiddy@exemail.com.au
                    Hi all, TOAD can also do this using Tools | Object Search. Enter your search string, Check the Column Names check box and select the Text Occurs Anywhere
                    Message 9 of 13 , Dec 2, 2005
                    • 0 Attachment
                      Hi all,
                      TOAD can also do this using Tools | Object Search.

                      Enter your search string,
                      Check the "Column Names" check box
                      and select the "Text Occurs Anywhere" option from the drop down.
                      You will get duplicate table names if it contains two columns containing
                      the search string.

                      cheers,
                      Paul

                      > I use a really simple SQL to check:
                      >
                      > SELECT owner, table_name, column_name FROM
                      > all_tab_columns WHERE column_name LIKE '%USER%'
                      >
                      >
                      >
                      > --- Chuck <skilover@...> wrote:
                      >
                      >> Rick wrote:
                      >>
                      >> >How can I use Toad to get a list of all tables that
                      >> have a column that
                      >> >has a particular string in it. For example, if I
                      >> wanted a list of all
                      >> >tables that have a column containing the text
                      >> "user", how would I do
                      >> >that?
                      >> >
                      >> >Thanks in advance.
                      >> >
                      >>
                      >> There are several ways to do it but all involve
                      >> dynamic SQL. Basically
                      >> you're going to need to write one SQL that's going
                      >> to generate one or
                      >> more other SQL's, then run the generated one(s). I'd
                      >> need more detail to
                      >> give more specific help.
                      >>
                      >> Will the column name be the same in all the tables
                      >> you want to check? Or
                      >> are you trying say "give me any column of any name
                      >> in any table that
                      >> contains the value "user"?
                      >>
                      >> Can I ask what the point of this exercise is? In ten
                      >> years as a DBA I
                      >> don't recall ever encountering this problem before.
                      >> That's not to say
                      >> that it can't be done, but it's highly unusual for a
                      >> relational database
                      >> application.
                      >>
                      >> Chuck
                      >>
                      >>
                      >>
                      >>
                      >>
                      >> Questions? Try the on-line FAQ:
                      >> http://www.toadsoft.com/faq2.html
                      >>
                      >> Do you know about the other TOAD Yahoo Message
                      >> Boards? Go here to find out more -
                      >> http://www.toadsoft.com/maillist.htm
                      >> Yahoo! Groups Links
                      >>
                      >>
                      >> toad-unsubscribe@yahoogroups.com
                      >>
                      >>
                      >>
                      >>
                      >>
                      >
                      >
                      >
                      >
                      > __________________________________________
                      > Yahoo! DSL – Something to write home about.
                      > Just $16.99/mo. or less.
                      > dsl.yahoo.com
                      >
                      >
                      >
                      > __________________________________________
                      > Yahoo! DSL – Something to write home about.
                      > Just $16.99/mo. or less.
                      > dsl.yahoo.com
                      >
                      >
                      >
                      >
                      >
                      >
                      > Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html
                      >
                      > Do you know about the other TOAD Yahoo Message Boards? Go here to find
                      > out more - http://www.toadsoft.com/maillist.htm
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                    • Chuck
                      Is the OP looking for a column *name* that has a particular string in it, or a column *value*. I thought he was looking for the latter. For example - find the
                      Message 10 of 13 , Dec 5, 2005
                      • 0 Attachment
                        Is the OP looking for a column *name* that has a particular string in
                        it, or a column *value*. I thought he was looking for the latter. For
                        example - find the value 12/25/2005 in any date column of any row of any
                        table in the database. I don't know of any option in toad or any other
                        tool to automate that. I think you need to use dynamic sql to do that.

                        Jesse, Rich wrote:

                        >Object Search is your friend! Tools->"Object Search"
                        >
                        >In the Options tab, uncheck everything but "Column Names" and make sure
                        >the dropdown says "Texr occurs anywhere" unless you want otherwise.
                        >Select the appropriate schema from the left hand side. Type "user"
                        >(sans quotes) in the "Search for" dropdown and click "Search".
                        >
                        ><ding> Search is done!
                        >
                        >HTH! Enjoy!
                        >
                        >Rich -- [TeamT]
                        >
                        >-----Original Message-----
                        >From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
                        >Rick
                        >Sent: Wednesday, November 30, 2005 1:37 PM
                        >To: toad@yahoogroups.com
                        >Subject: [toad] How do I list all tables that have a particular column
                        >
                        >
                        >How can I use Toad to get a list of all tables that have a column that
                        >has a particular string in it. For example, if I wanted a list of all
                        >tables that have a column containing the text "user", how would I do
                        >that?
                        >
                        >Thanks in advance.
                        >
                        >
                        >Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html
                        >
                        >Do you know about the other TOAD Yahoo Message Boards? Go here to find out more - http://www.toadsoft.com/maillist.htm
                        >Yahoo! Groups Links
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                        >
                      • Ed Klinger
                        Since the OP never mentioned *value* and never replied to the dozen or so responses to this thread it is probably safe to say he found what he was looking for.
                        Message 11 of 13 , Dec 5, 2005
                        • 0 Attachment
                          Since the OP never mentioned *value* and never replied to the dozen or so
                          responses to this thread it is probably safe to say he found what he was
                          looking for.

                          Ed
                          [TeamT]


                          -----Original Message-----
                          From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of Chuck
                          Sent: Monday, December 05, 2005 10:20 AM
                          To: toad@yahoogroups.com
                          Subject: Re: [toad] How do I list all tables that have a particular column

                          Is the OP looking for a column *name* that has a particular string in it, or
                          a column *value*. I thought he was looking for the latter. For example -
                          find the value 12/25/2005 in any date column of any row of any table in the
                          database. I don't know of any option in toad or any other tool to automate
                          that. I think you need to use dynamic sql to do that.

                          Jesse, Rich wrote:

                          >Object Search is your friend! Tools->"Object Search"
                          >
                          >In the Options tab, uncheck everything but "Column Names" and make sure
                          >the dropdown says "Texr occurs anywhere" unless you want otherwise.
                          >Select the appropriate schema from the left hand side. Type "user"
                          >(sans quotes) in the "Search for" dropdown and click "Search".
                          >
                          ><ding> Search is done!
                          >
                          >HTH! Enjoy!
                          >
                          >Rich -- [TeamT]
                          >
                          >-----Original Message-----
                          >From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
                          >Rick
                          >Sent: Wednesday, November 30, 2005 1:37 PM
                          >To: toad@yahoogroups.com
                          >Subject: [toad] How do I list all tables that have a particular column
                          >
                          >
                          >How can I use Toad to get a list of all tables that have a column that
                          >has a particular string in it. For example, if I wanted a list of all
                          >tables that have a column containing the text "user", how would I do
                          >that?
                          >
                          >Thanks in advance.
                          >
                          >
                          >Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html
                          >
                          >Do you know about the other TOAD Yahoo Message Boards? Go here to find
                          >out more - http://www.toadsoft.com/maillist.htm
                          >Yahoo! Groups Links
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >





                          Questions? Try the on-line FAQ: http://www.toadsoft.com/faq2.html

                          Do you know about the other TOAD Yahoo Message Boards? Go here to find out
                          more - http://www.toadsoft.com/maillist.htm
                          Yahoo! Groups Links








                          **************************************************************************
                          Notice of Confidentiality: This e-mail communication and the attachments hereto, if any, are intended solely
                          for the information and use of the addressee(s) identified above and may contain information which is legally
                          privileged and/or otherwise confidential. If a recipient of this e-mail communication is not an addressee (or an
                          authorized representative of an addressee), such recipient is hereby advised that any review, disclosure,
                          reproduction, re-transmission or other dissemination or use of this e-mail communication (or any information
                          contained herein) is strictly prohibited. If you are not an addressee and have received this e-mail communication
                          in error, please advise the sender of that circumstance either by reply e-mail or by telephone at (407) 384-8818
                          immediately delete this e-mail communication from any computer and destroy all physical copies of same.

                          Replies Filtered: Any incoming e-mail reply to this communication will be electronically filtered for "spam"
                          and/or "viruses." That filtering process may result in such reply being quarantined (i.e., potentially not received
                          at our site at all) and/or delayed in reaching us. For that reason, we cannot guarantee that we will receive your
                          reply and/or that we will receive it in a timely manner. Accordingly, you should consider sending communications
                          to us which are particularly important or time-sensitive by means other than e-mail.
                          **************************************************************************
                        Your message has been successfully submitted and would be delivered to recipients shortly.