Here is query for this.
In this query we want to find column "ProductID" in all tables and get list of that.
SQL Query :
select sysobjects.name, * from syscolumns, sysobjects where syscolumns.name='ProductID' and sysobjects.id = syscolumns.id and (sysobjects.xtype='U' or sysobjects.xtype='S')
This is the very useful SQL Server Scripts.
Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.