• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1700
  • Last Modified:

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!
0
dstjohnjr
Asked:
dstjohnjr
2 Solutions
 
Racim BOUDJAKDJIDatabase 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 MangusDatabase 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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now