I'm searching for the right column in a large Microsoft SQL database. Is there a way to find all columns with a certain name without manually browsing through all tables?
Solution
A colleague once gave me this handy query. It searches for column names in an entire Microsoft SQL database. An indispensable query when you have to do a little research while building SSIS packages.
--Search for column name in database
SELECT tab.name as [Table/View]
, CASE
WHEN tab.xtype = 'V' THEN 'view'
ELSE 'table'
END as [Type]
, col.name as [ColumnName]
, typ.name as [Datatype]
FROM dbo.syscolumns as col
, dbo.sysobjects as tab
, dbo.systypes as typ
WHERE col.id = tab.id
AND col.xtype = typ.xtype
AND tab.xtype in ('V','U')
-- Pick on of these possibilities
--AND col.name = 'version'
--AND col.name like '%version%'
Select the right database and change commented out lines.
 |
| The result |