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.