Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query to find a specific field ina a single databse

Posted on 2006-07-05
9
Medium Priority
?
438 Views
Last Modified: 2008-01-09
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

0
Comment
Question by:Okie13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 12

Accepted Solution

by:
Einstine98 earned 900 total points
ID: 17046692
use sysobjects to find the tables
sysdepends to find dependencies
syscolumns to locate a specific column

example

SELET O.Name
FROM sysobjects AS O
JOIN syscolumns AS C
on C.id = O.ID
WHERE C.Name = 'company'
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 800 total points
ID: 17047574
try:
INFORMATION_SCHEMA.COLUMNs view: http://msdn2.microsoft.com/en-us/library/ms188348.aspx

exec master..sp_msforeachdb 'use ? select * from INFORMATION_SCHEMA.COLUMNs where column_name=''company'''
++++++++++++++++++++++++=
or\and
undocumented solution:

exec master.dbo.sp_MSobjsearch @searchkey = N'company', @objecttype = 1024, @dbname = [*], @casesensitive = 0, @hitlimit = 1000, @status = 1, @extpropname = NULL, @extpropvalue = NULL
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 800 total points
ID: 17047581
also there is GUI solution:
open Query Analyzer and press F4 (find object)...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:Eugene Z
ID: 17047585
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'
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 300 total points
ID: 17047706
SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Company'
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17049446
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 ...'
                                                   --------^----------
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17049577
You are right EugeneZ...
"DISTICT" is not really needed in that.. Also I didn't See your previous post ... .
0
 
LVL 35

Expert Comment

by:James0628
ID: 17049671
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
0
 

Author Comment

by:Okie13
ID: 17050707
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question