Select table for a specific column name

umasankaran · #1 (**permalink**) 01-17-06, 01:35

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?

healdem · #2 (**permalink**) 01-17-06, 03:26

depending on the SQL engine you are using you should be able to identify these using the schema tables, or system tables.

umasankaran · #3 (**permalink**) 01-18-06, 00:32

depending upon system table i would like to search table

Littlefoot · #4 (**permalink**) 01-18-06, 16:06

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.

kropes2001 · #5 (**permalink**) 01-19-06, 06:05

how do i delete a duplicate post ?

kropes2001 · #6 (**permalink**) 01-19-06, 06:05

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...

Peter.Vanroose · #7 (**permalink**) 01-20-06, 08:48

In DB2 this would be

Code:

SELECT rtrim(tbcreator) || '.' || tbname
FROM   sysibm.syscolumns
WHERE  name = 'EMPNO'

Internet Services

Web Development

Digital Marketing

Consumer Tech