Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    india
    Posts
    5

    Exclamation Unanswered: Select table for a specific column name

    i would like to find out the table from all the tables in my database which is having same field name .

    Eg.

    i would like to find out the tables from all my tables containing field name customer_name.

    please give the sql for this type of problem?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    depending on the SQL engine you are using you should be able to identify these using the schema tables, or system tables.

  3. #3
    Join Date
    Jan 2004
    Location
    india
    Posts
    5

    Post

    depending upon system table i would like to search table

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, but what database do you work with? In Oracle, you could try with
    Code:
    SQL> select table_name from all_tab_columns where column_name = 'EMPNO';
    
    TABLE_NAME
    ------------------------------
    EMP
    
    SQL>
    but I doubt that there's "all_tab_columns" table in SQL Server or MySQL or DB2 or ... All those databases surely have similar table, but it is impossible to suggest a correct answer until you provide more information.

  5. #5
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    how do i delete a duplicate post ?
    Last edited by kropes2001; 01-19-06 at 07:10.
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  6. #6
    Join Date
    Nov 2005
    Location
    Honolulu HI
    Posts
    119
    in MS SQL you can use :
    Set rs = Connection.OpenSchema(20)
    that will give you a listing of all of the table names in a recordset.

    then you loop through them and get a list of the fields in each table

    Code:
    <html>
    <body>
    
    	<%
    	Set rs = TheDatabase.OpenSchema(20)
    	if not rs.eof then
    		rs.MoveFirst
    		do while Not rs.eof
    		strTableType=rs.fields("TABLE_TYPE")
    		if strTableType="TABLE" then
    			strTableName=rs.fields("TABLE_NAME")
    
    			SQL="SELECT TOP 1 * FROM " & strTableName
    			Set rsData = TheDatabase.Execute(SQL)
    	%>
    			<table cellspacing=0 border=1>
    				<tr nowrap><%	
    					intFieldCount=rsData.fields.count-1
    					for iintFieldCounter = 0 to intFieldCount
    						FieldName=rsData.fields(iintFieldCounter).name 
    					%>
    					<td><center><font color="<%=strTableHeaderTextColor%>">&nbsp;&nbsp;<%=FieldName%>&nbsp;&nbsp;</td>	<%
    					next%>
    				</tr>
    			</tabel><br>
    			<%=strTableName%>&nbsp;&nbsp;&nbsp;(<%=strTableType%>)
    
    	<%
    			end if
    		rs.MoveNext
    		loop
    	end if
    	rs.Close
    
    	%>
    
    </body>
    </html>
    of course you can change this a round a bit to do what you need...
    .
    .
    http://www.HawaiianHope.org
    Providing Technology services to non profit orgs, Homeless Shelters, Food Pantries, Clean And Sober Houses and more.
    To date we have given away over 900 free computers !
    __________________________
    caeli enarrant gloriam Dei !

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    In DB2 this would be
    Code:
    SELECT rtrim(tbcreator) || '.' || tbname
    FROM   sysibm.syscolumns
    WHERE  name = 'EMPNO'
    Last edited by Peter.Vanroose; 01-20-06 at 09:54.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •