Sometimes you know the field name, but forget about in which table it is.
This small procedure will help you ;).

USE AdventureWorks — the database must be specified
GO
CREATE PROCEDURE procAllTableswithField(@columnname nvarchar(400)='%')
AS
SELECT OBJECT_NAME(sys.columns.object_id) AS "Table",
RTRIM(sys.columns.name) AS "Column"
FROM sys.columns INNER JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.name Like @columnname AND sys.objects.type = 'u'

--- list all tables with specified field
EXEC procAllTableswithField 'CustomerID'

EXEC procAllTableswithField 'cust%'

EXEC procAllTableswithField


You can download it here ;)