Retrieve table list where the given column name exists

Select object_name from syscolumns where name = @yourColumnName

this query is very handy at times when we need to retrieve the list of tables where the given column name exits