If you believe a particular field may exist somewhere in your database but you don’t know the table or view it may appear in (or you want to do an inventory of how often and where a field appears), there’s information about that in sys.all_objects and sys.all_columns. Say, you’ve found a table with a UserID column in it and can’t find the table that matches that UserID with a real name. (It should be pointed out that the following script does rely on the database designer having used the same field name in both tables.)
You’ll need to replace “SEC_NAME” with the field name/portion thereof you’re looking for.
SELECT sys.all_objects.name AS [Table/View], sys.all_columns.name AS [Column], sys.all_objects.type_desc AS [Type]
FROM sys.all_objects
INNER JOIN sys.all_columns
ON sys.all_objects.object_id = sys.all_columns.object_id
WHERE (sys.all_objects.type = ‘V’ OR sys.all_objects.type = ‘U’) AND sys.all_columns.name LIKE ‘%SEC_NAME%’
ORDER BY sys.all_objects.name, sys.all_columns.name