Find any Column in a SQL Server Database
Let's say we're working with a Microsoft SQL Server and we have no idea what table or column stores a particular value. Trying to find a database column where we know what the name of it might be, at least partially, is not impossible.
In this example, we've identified the data is referenced in our application code with the identifier "PropertyABC". Running the following query will find all database columns that match "Property" and tell us what tables they're in.
SELECT s.[name] 'Schema', t.[name] 'Table', c.[name] 'Column', d.[name] 'Data Type', d.[max_length] 'Max Length', d.[precision] 'Precision', c.[is_identity] 'Is Id', c.[is_nullable] 'Is Nullable', c.[is_computed] 'Is Computed', d.[is_user_defined] 'Is UserDefined', t.[modify_date] 'Date Modified', t.[create_date] 'Date created' FROM sys.schemas s WITH (NOLOCK) INNER JOIN sys.tables t WITH (NOLOCK) ON s.schema_id = t.schema_id INNER JOIN sys.columns c WITH (NOLOCK) ON t.object_id = c.object_id INNER JOIN sys.types d WITH (NOLOCK) ON c.user_type_id = d.user_type_id WHERE c.name LIKE '%Property%'
Similarly, once we know the database column name we can search further into stored proceedures and user defined functions to see where they're being referenced.
Let's assume that we found the column using the query above and its name is actually "Property_ABC". What we want to know now are all the proceedures or functions that either set or get the value in this column.
We can get this information using the following query.
SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%Property_ABC%'
We'll see probably several objects (stored proceedures and functions) along with their definitions. We can then read through these definitions to see what is changing or referencing the column.