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!
My platform is MS SQL Server 2000.
TIA for any help on this!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
*************** 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
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
ASKER