Solved

SQL Query to locate a specific known field name across all tables in a DB

Posted on 2007-11-27
4
1,684 Views
Last Modified: 2008-02-01
I am looking to design a query that searches all tables in a DB to find all instances of a specific field name throughout the entire DB.

My platform is MS SQL Server 2000.

TIA for any help on this!
0
Comment
Question by:dstjohnjr
[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
4 Comments
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 250 total points
ID: 20362239
use yourdb
go

select so.name database_name, sc.name from sysobjects so inner join syscolumns sc on so.id = sc.id
where sc.name like '%somecolumnname%'

Hope this helps...
0
 
LVL 17

Assisted Solution

by:Chris Mangus
Chris Mangus earned 250 total points
ID: 20362247
select so.name, sc.name
from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name = 'mycolumnname'

Run this from the database in question...
0
 

Author Comment

by:dstjohnjr
ID: 20362273
Thank you experts!  Both solutions worked exactly as needed.  Thanks again!
0
 
LVL 15

Expert Comment

by:spprivate
ID: 20362286
*************** 1. List tables, columns by Column(table) name
Useful to list all tables where column name matched with value specified in WHERE CLAUSE.
For example, list all tables where a column name contains “order” as (a part of) its name.
*****************/
SELECT o.name as [Table], c.name as [Column]
FROM SYSOBJECTS o JOIN SYSCOLUMNS c ON o.id = c.id
WHERE c.name  like '%order%' AND o.xtype = 'u'
ORDER BY 1
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

Suggested Solutions

Title # Comments Views Activity
MSSQL Query for Selecting the SUM of a Specific Group 2 33
HIghlights of SSIS? 3 40
Search Text in Views 2 24
how to use ROW_NUMBER() correctly 8 40
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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