[RESOLVED] Searching field

I am working on SQL. How can I find a specific field is available in how many table in specific database

If you're using dql server then try this statement:

VB Code:

select table_name from information_schema.columns
where column_name = 'user_id'
 
NOTE: you may also need to add to the "where" clause the following columns to get more specific results:
 
table_catalog
table_schema

Thanx for ur rpl.

I just want to view specific field in database and also in how many tables that field exist. im not searching in table it's in whole database

On your ADO connection object

VB Code:

Dim rs As ADODB.Recordset
Dim cnt As Long
 
sColName = 'field name to search
Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, sColName)
If Not (rs Is Nothing) then
   Do Until rs.EOF
      Debug.Print rs.Fields("TABLE_NAME").Value & ""  'if you want to know what tables have this field
      cnt = cnt + 1
      rs.MoveNext
   Loop
   rs.Close
End If
 
Debug.Print cnt

The columns returned in rs are:
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME

thanks leinad31

What this line do?

"Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, sColName)"

and what is cn?

cn is the ADO connection object ... I usually declare it as

VB Code:

Public cn As New ADODB.Connection

OpenSchema() method of the connection object returns a recordset whose fields depends on the arguments supplied. First argument, adSchemaColumns tells OpenSchema that we want to get schema information of columns, schema info for all columns is returned if none is specified in second argument... Second argument is an array that corresponds to adSchemaColumns fields TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. In the array, we didn't specify TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME or we passed Empty... we specified column name for COLUMN_NAME in Array().

Hence OpenSchema returns all schema records/info for column we specified with sColName variable. We need to assign to a reference the recordset returned by the connection object, hence Set rs = cn.... we can then manipulate the recordset in succeeding lines.

BTW, that line lacks one more closing parenthesis

Set rs = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, Empty, sColName))

Thanx again

Can you elaborate more about OpenSchema() method?
This Is very complex for me can u give me simple SQL query that I can run in SQL?

It returns schema information, that's all you need to know... its similar to what Rhino posted in that sense... but unlike Rhino's code where you have to know the name of the system table in the database that contains the schema information for the column, the OpenSchema method of the ado connection object spares you from that detail... you simply tell it what schema information you want based on the first argument, the second argument is only for additional criteria such as what we did specifying a column name for the adSchemaColumsn argument, we didn't need to know in which table the database stores the schema information.

http://msdn.microsoft.com/library/de...thod_oledb.asp

Quote:

---

Originally Posted by leinad31

... but unlike Rhino's code where you have to know the name of the system table in the database that contains the schema information for the column,...

---

So what's the big deal - it's your database name:

VB Code:

select table_name
from information_schema.columns
where column_name = 'user_id'
and table_catalog = 'database_name'

That simplest sql you can possible have (in this case) that you can execute via ADO will return all tables containing fields (or columns) named USER_ID.
So, what else would you need???????

Just FYI: information_schema.columns is a system view from master db was designed for this particular reason - get you quick info on specific column, table, etc...

Because that table/view name is database specific...

What are you talking about?! :rolleyes:
That is a system view and I did mention it - it belongs to a master database. So, if you won't specify the table_catalog (just like I originally posted) you will get list of all tables from the entire server from all databases that have that field you're interested.
To search specific database simply specify the table_catalog.
Common really... If you have something more constructive to say then I will be very happy to learn.

Regards.

Quote:

---

Originally Posted by RhinoBull

What are you talking about?! :rolleyes:
That is a system view and I did mention it - it belongs to a master database. So, if you won't specify the table_catalog (just like I originally posted) you will get list of all tables from the entire server from all databases that have that field you're interested.
To search specific database simply specify the table_catalog.
Common really... If you have something more constructive to say then I will be very happy to learn.

Regards.

---

Please make this work for me (cant find similar system table) so I can learn... I'm very happy to do so.

And I never said you were wrong since he was using sql server, I simply pointed out the method is independent of the database type and knowledge of the system tables. I mentioned your method, using select, since he asked for an explanation... I thought it would be easier for him to understand the creation by openschema() of a recordset object if he can tie it up to your select query.

He is working with SQL Server - he never mentioned MS Access... Read his original post.

Quote:

---

Originally Posted by xor83

I am working on SQL. How can I find a specific field is available in how many table in specific database

---

I didn't need to know what database he was using... that was the other point I was trying to make.

OH WELL... This has dragged on to long... xor83, please don't consider the openschema method and use the select query instead so that RhinoBull can have his way and this thread can be put to rest.

Quote:

---

Originally Posted by leinad31

... xor83, please don't consider the openschema method and use the select query instead so that RhinoBull can have his way and this thread can be put to rest.

---

@leinad31:
I'm sorry that you don't realize that there are other than MS Access database engines exist and each of them offers unique functionality. You have to learn how to take the most of it.

@xor83:
do youself a big favor and try not to limit your development to MS Access type thing...

Good luck to both of you.

Quote:

---

Originally Posted by RhinoBull

@leinad31:
I'm sorry that you don't realize that there are other than MS Access database engines exist and each of them offers unique functionality. You have to learn how to take the most of it.

---

Just because I'm not much into proprietary approaches doesn't mean I am not familiar with other database formats... but who am I to get into a debate with a know it all?

LOL... How is it "proprietary" if it's been used by millions and you also have a source? MS SQL Server has been arround since 1989 and originally derived from SYBASE SQL Server. MS SQL, Oracle, SYBASE are pretty much industry's standards and in today's world you must know at least one (better two as most corps are using multiple) of those engines to survive in this business.
And I never said you're not familiar - you just seemed to play "stubborn" a bit. :) :wave:

Quote:

---

Originally Posted by RhinoBull

LOL... How is it "proprietary" if it's been used by millions and you also have a source? MS SQL Server has been arround since 1989 and originally derived from SYBASE SQL Server. MS SQL, Oracle, SYBASE are pretty much industry's standards and in today's world you must know at least one (better two as most corps are using multiple) of those engines to survive in this business.
And I never said you're not familiar - you just seemed to play "stubborn" a bit. :) :wave:

---

You can do their marketing for them.. no prob...

but in the end, I don't bother with such details on accessing the schema progmatically, you make it sound like its done on an hourly basis and everyone has to know that query by heart. :rolleyes:

Molehills into mountains...

Quote:

---

Originally Posted by leinad31

... you make it sound like its done on an hourly basis and everyone has to know that query by heart...

---

You have to if you work with the product on a "daily" basis - it's the same as remembering the Mid$, Left$, Right$, InStr()...
You either know the product or you learn if you don't .

BUt this would be it for me - pointless debate.

Yes its your surrogate wife...

And comparing frequency of use of Mid$, Left$, Right$, InStr to frequency of querying the schema (which was the issue of the pointless debate you started) is silly.

Quote:

---

Originally Posted by RhinoBull

Sorry, but you have to learn how to keep your temper and be civilized. RB

---

comment on a negative rep point. Wow, only he has the right to post sarcasm... self righteous til the very end.

Your were no better after post #13, you avoided this

Quote:

---

And I never said you were wrong since he was using sql server, I simply pointed out the method is independent of the database type and knowledge of the system tables. I mentioned your method, using select, since he asked for an explanation... I thought it would be easier for him to understand the creation by openschema() of a recordset object if he can tie it up to your select query.

---

to make a fuss out of access.

Har har har mountains out of molehills.

Peace.... don’t fight... :bigyello:

thanx
leinad31 and RhinoBull ... :thumb:

finnaly i able to find what i am looking for ......