Solved

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

Posted on 2007-11-27
4
1,681 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
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

808 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