Renaming database objects in SQL Server 2005

Naming conventions zijn goed. Alleen jammer als je achteraf alles moet aanpassen. Hieronder vind je een paar scripts om kolommen, tabellen en constraints te renamen.

--Rename primary keys
--Remove Dim from column name
SELECT
'EXEC sp_rename ''HR.' + TABLE_NAME + '.'
+ COLUMN_NAME + ''', ''' + SUBSTRING(COLUMN_NAME,4, 64) + ''', ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'HR'
AND TABLE_NAME LIKE 'Dim%'
AND ORDINAL_POSITION = 1

--Rename tables
--Remove Dim from table name
SELECT
'EXEC sp_rename ''HR.' + TABLE_NAME +
''', ''' + SUBSTRING(TABLE_NAME,4, 64) + ''', ''OBJECT'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'HR'
AND TABLE_NAME LIKE 'Dim%'
AND ORDINAL_POSITION = 1


--Rename foreign keys
--FK_ChildTable_ParentTable
SELECT 'exec sp_rename ''HR.' + name + ' '',''FK_'
+ object_name(parent_object_id) + '_'
+ object_name(referenced_object_id) + ''''
FROM sys.foreign_keys
WHERE SCHEMA_ID = SCHEMA_ID('HR')

--Rename Primary keys
--PK_Table
SELECT 'exec sp_rename ''HR.' + name + ' '',''PK_'
+ object_name(parent_object_id) + ''''
FROM sys.key_constraints
WHERE SCHEMA_ID = SCHEMA_ID('HR')
AND [TYPE] = 'PK'

--Rename default
--DF_Table_Column
SELECT 'exec sp_rename ''HR.' + d.name + ' '',''DF_' +
+ object_name(parent_object_id) + '_' + c.name +''''
FROM sys.default_constraints d
INNER JOIN sys.columns c
ON d.parent_object_id = c.object_id
AND d.parent_column_id = c.column_id
WHERE SCHEMA_ID = SCHEMA_ID('HR')