Search ALL tables for a specific column

Let's say you need to search for all tables in your database that have a column named 'name'.  Using AdventureWorks2012, here's a quick way to do it:

    SELECT
        t.name [Table],
        SCHEMA_NAME(schema_id) [Schema],
        c.name [Column]
    FROM
        sys.tables t INNER JOIN sys.columns c
          ON t.OBJECT_ID = c.OBJECT_ID
    WHERE
        c.name = 'name'     
    ORDER BY
        [Table],
        [Schema]

What if you want to rename the column in all tables?  You can use this statement to RENAME the specified column in all of the identified tables:

   SELECT
'EXEC sp_rename ''' + sc.name + '.' + tb.name + '.' + col.name + ''',''' + tb.name + col.name +''''+ ',N''COLUMN'''  
   FROM      
       sys.tables tb INNER JOIN sys.schemas sc
         ON tb.schema_id = sc.schema_id INNER JOIN sys.columns col
           ON tb.object_id = col.object_id
    WHERE      
       col.name = 'name'
    ORDER BY
       sc.name

I am using ORDER BY purely for visibility, but you can see that I am renaming all 'name' columns to TableName+Name, like this:

    EXEC sp_rename 'Production.Product.Name','ProductName',N'COLUMN'
    EXEC sp_rename 'HumanResources.Shift.Name','ShiftName',N'COLUMN'

But remember, this will ONLY work on those tables without dependencies.  Anything with dependencies will cause a failure.  For example, try to rename the column in the Product table:  

    EXEC sp_rename 'Production.Product.Name' ,'ProductName'

It will fail with this: 

  Msg 15336, Level 16, State 1, Procedure sp_rename, Line 497 
Object 'Production.Product.Name' cannot be renamed because the object participates in enforced dependencies. 

That error returns because the Product.Name column is referenced in three functions, two procedures and a view.  To my knowledge, there is no way to force your way around this.  The dependencies will need to be dropped first, and then recreated after you rename the column.

You can use sys.dm_sql_referencing_entities to identify all Product table dependencies:

    SELECT
        referencing_schema_name [Schema],
        referencing_entity_name [ReferencingObject]
    FROM
        sys.dm_sql_referencing_entities ('Production.Product', 'OBJECT')

Those objects would need to be altered before Product.Name can be renamed.

That's it.  Search for a column in all tables, or rename it... again, in all tables.  Obviously, be sure you've got a backup before making any changes.  :-)

See both of these for more detail:  

    sys.dm_sql_referencing_entities
         http://technet.microsoft.com/en-us/library/bb630351.aspx
    Reserved Key Words  
       http://technet.microsoft.com/en-us/library/ms189822.aspx