asked on

Query to find a specific field ina a single databse

I am in need of a solution to find a specific field, and how many times it is used, referenced throughout a databse

There are 2064 tables in databse A - I want to find out which tables have the 'company' field in this database. Is there a query that can be written to find all of the tables that use 'company' as a field in databse A. I do not care what the data is at this time, just want to know the location of the field without having to query each table.

Thank you in advance for your assistance.

Greg

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

BTW:
the solution for all databases:
exec master..sp_msforeachdb 'use ? select * from INFORMATION_SCHEMA.COLUMNs where column_name=''company'''

you need just:
use yourDBname
select * from INFORMATION_SCHEMA.COLUMNs where column_name='company'

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

aneeshattingal:
why do you need "DISTINCT"? How many COLUMN_NAME = 'Company' can be per table?
It is just and One. No need in 'SELECT DISTINCT TABLE_NAME ...'
--------^----------

Aneesh

You are right EugeneZ...
"DISTICT" is not really needed in that.. Also I didn't See your previous post ... .

James0628

You can find a stored procedure named sp_FindColumn at the following URL. It will let you search for one or more columns by name or partial name, and some other options.

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1109458,00.html

James

Okie13

ASKER

Thanks to all of you - each your answers worked perfectly and really helped me out - I gave the most to Einstine98 for being first ... EugeneZ: Thanks for the GUI tip - I liked that ome since I am in it so often.

As I said all of the answers worked and I really do appreciate it!!

Greg