SQL Query - To search for a column (by column name) in all tables/ views within a database

At times you need to know if a column of a specific name do exist in any table/ view of your SQL Server database.
In that case, we have certain options to browse throgh each table, or to generate script of all tables (in single go) and to do find operation. But its a time-consuming job.

As such, SQL Server stores information about any database object (i.e. column,index, table,SPs,etc) in system tables. So following SQL query can be very handy at times:


USE DatabaseName
select * from sys.objects where object_id in
(select object_id from sys.columns where name = @column_name)


This will return a resultset containing list of all tables which are having a column of the particular name. You can also perform wild-card search etc here.