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