Thursday, January 16, 2014

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 [Table],
        SCHEMA_NAME(schema_id) [Schema], [Column]
        sys.tables t INNER JOIN sys.columns c
          ON t.OBJECT_ID = c.OBJECT_ID
    WHERE = 'name'     

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:

'EXEC sp_rename ''' + + '.' + + '.' + + ''',''' + + +''''+ ',N''COLUMN'''  
       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      = '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:

        referencing_schema_name [Schema],
        referencing_entity_name [ReferencingObject]
        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:  

    Reserved Key Words

No comments:

Post a Comment