[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2007-11-27
4
Medium Priority
?
1,689 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 1000 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 1000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

649 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