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

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!
dstjohnjrAsked:
Who is Participating?
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
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
 
Chris MangusConnect With a Mentor Database AdministratorCommented:
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
 
dstjohnjrAuthor Commented:
Thank you experts!  Both solutions worked exactly as needed.  Thanks again!
0
 
spprivateCommented:
*************** 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.