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.