February 16, 2005 at 4:20 am
Does anyone know of a tool that will allow me to change a table name and also some of the field names within that table, and change the fields in the dependency tables aswell.
February 16, 2005 at 7:12 am
check out
SP_Rename 'OldTableName', 'NewTableName', 'OBJECT'
SP_Rename 'dbo.Table.ColName', 'NewName', 'COLUMN'
February 16, 2005 at 7:39 am
Thanks, I am looking for some 3rd party tool that will automatically change the identical field names in the dependency tables aswell.
February 16, 2005 at 8:20 am
Check this out : it lists the relation name along with the table names and columns of the pk and and fks
SELECT TOP 100 PERCENT ORelations.name AS RelationName, OParentTables.name AS ParentTableName, Parents.name AS PkColName,
OChildTables.name AS ChildTable, Childs.name AS FkColName
FROM dbo.sysforeignkeys PKS INNER JOIN
dbo.syscolumns Parents ON PKS.rkeyid = Parents.id AND PKS.rkey = Parents.colid INNER JOIN
dbo.syscolumns Childs ON PKS.fkeyid = Childs.id AND PKS.fkey = Childs.colid INNER JOIN
dbo.sysobjects ORelations ON PKS.constid = ORelations.id INNER JOIN
dbo.sysobjects OParentTables ON Parents.id = OParentTables.id INNER JOIN
dbo.sysobjects OChildTables ON Childs.id = OChildTables.id
WHEREParents.name = Childs.name
ORDER BY OParentTables.name, OChildTables.name
From there it's not too hard to generate something like this :
if object_id('tempNames') > 0
drop table tempNames
GO
SELECT TOP 100 PERCENT ORelations.name AS RelationName, OParentTables.name AS ParentTableName, Parents.name AS PkColName,
OChildTables.name AS ChildTable, Childs.name AS FkColName, 'New_name' + Parents.name as NewColName
INTO tempNames
FROM dbo.sysforeignkeys PKS INNER JOIN
dbo.syscolumns Parents ON PKS.rkeyid = Parents.id AND PKS.rkey = Parents.colid INNER JOIN
dbo.syscolumns Childs ON PKS.fkeyid = Childs.id AND PKS.fkey = Childs.colid INNER JOIN
dbo.sysobjects ORelations ON PKS.constid = ORelations.id INNER JOIN
dbo.sysobjects OParentTables ON Parents.id = OParentTables.id INNER JOIN
dbo.sysobjects OChildTables ON Childs.id = OChildTables.id
WHEREParents.name = Childs.name
ORDER BY OParentTables.name, OChildTables.name
Select * from tempNames
--place the columns' new name in the NewName column using Enterprise manager
--run this in query analyser
Select /*dtRenameQrys.ParentTableName, */dtRenameQrys.RenameQry FROM(
SELECT DISTINCT ParentTableName, 'EXEC SP_Rename ''dbo.[' + ParentTableName + '].[' + PkColName + ']'', ''[' + NewColName + ']'', ''COLUMN''' as RenameQry FROM tempNames
UNION ALL
SELECT DISTINCT ParentTableName, 'EXEC SP_Rename ''dbo.[' + ChildTable + '].[' + FkColName + ']'', ''[' + NewColName + ']'', ''COLUMN''' as RenameQry FROM tempNames
) dtRenameQrys
ORDER BY dtRenameQrys.ParentTableName
/* sample output
EXEC SP_Rename 'dbo.[_Temps_tech_repas].[N° Bon de travail]', '[New_nameN° Bon de travail]', 'COLUMN'
EXEC SP_Rename 'dbo.[Bon de travail].[N° Bon de travail]', '[New_nameN° Bon de travail]', 'COLUMN'
EXEC SP_Rename 'dbo.[liste_mo_table].[N° Bon de travail]', '[New_nameN° Bon de travail]', 'COLUMN'
EXEC SP_Rename 'dbo.[Pièces].[N° Bon de travail]', '[New_nameN° Bon de travail]', 'COLUMN'
EXEC SP_Rename 'dbo.[80pourcent].[no client]', '[New_nameno client]', 'COLUMN'
EXEC SP_Rename 'dbo.[CLIENT].[no client]', '[New_nameno client]', 'COLUMN'
EXEC SP_Rename 'dbo.[Commentaire par client].[No Client]', '[New_nameno client]', 'COLUMN'
EXEC SP_Rename 'dbo.[CLIENT].[Ville]', '[New_nameVille]', 'COLUMN'
EXEC SP_Rename 'dbo.[KmVille].[Ville]', '[New_nameVille]', 'COLUMN'
EXEC SP_Rename 'dbo.[Horaire].[Technicien]', '[New_nameTechnicien]', 'COLUMN'
EXEC SP_Rename 'dbo.[Techniciens].[NoTech]', '[New_nameNoTech]', 'COLUMN'
EXEC SP_Rename 'dbo.[Techniciens].[Technicien]', '[New_nameTechnicien]', 'COLUMN'
EXEC SP_Rename 'dbo.[TechPosition].[NoTech]', '[New_nameNoTech]', 'COLUMN'
*/
--paste the output from that query in query analyser and run it
drop table tempNames
February 16, 2005 at 8:23 am
I'll give it a go, thanks a million...
February 17, 2005 at 12:14 am
You may want to check out database change management software. DB Ghost (http://www.dbghost.com) will help you create a perfect release by identifying any problems in your source code through compilation.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply