Search This Blog

SQL: 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