Tuesday 25 January 2011

Search for ColumnName

Case
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

No comments:

Post a Comment

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...