Renombrar proyectos en Project Server 2003

Es muy común que un proyecto sea dado de alta de una manera errónea y sea necesario cambiar el nombre del proyecto una vez que fue publicado. Este nombre es utilizado en muchas tablas en la base de datos, y para realizar una cambio consistente se puede ejecutar el siguiente script de SQL que realiza la actualización en las tablas necesarias.


Declare @newName VARCHAR(100)
Declare @nProjID INT

select @nProjID = proj_id from msp_web_projects where proj_name = 'Antiguo nombre de proyecto'

set @newName = 'Nuevo nombre del proyecto'

--SCRIPT

--Update MSP_PROJECTS
UPDATE MSP_PROJECTS
SET PROJ_NAME=@newName + (SUBSTRING(PROJ_NAME,CHARINDEX('.',PROJ_NAME),LEN(PROJ_NAME))),
PROJ_PROP_TITLE=@newName + (SUBSTRING(PROJ_PROP_TITLE,CHARINDEX('.',PROJ_PROP_TITLE),LEN(PROJ_PROP_TITLE))),
PROJ_PROJECT='<>\' + @newName,
PROJ_EXT_EDITED=1
WHERE PROJ_ID= @nProjID
OR PROJ_ID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID)))

--Update MSP_WEB_PROJECTS
UPDATE MSP_WEB_PROJECTS
SET PROJ_NAME=@newName,
WLOCAL_PATH='<>\' + @newName + '.Published'
WHERE PROJ_ID= @nProjID
OR PROJ_ID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID)))

--Update MSP_VIEW_PROJ_PROJECTS_STD
UPDATE MSP_VIEW_PROJ_PROJECTS_STD
SET ProjectTitle= @newName + (SUBSTRING(ProjectTitle,CHARINDEX('.',ProjectTitle),LEN(ProjectTitle)))
WHERE ProjectUniqueID= @nProjID
OR ProjectUniqueID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID)))

--Update MSP_VIEW_PROJ_TASKS_STD
UPDATE MSP_VIEW_PROJ_TASKS_STD
SET TaskName=@newName + (SUBSTRING(TaskName,CHARINDEX('.',TaskName),LEN(TaskName)))
WHERE TaskUniqueID=0
And (ProjectUniqueID= @nProjID
OR ProjectUniqueID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID))) )

--Update MSP_WEB_ASSIGNMENTS
UPDATE MSP_WEB_ASSIGNMENTS
SET TASK_NAME=@newName + (SUBSTRING(TASK_NAME,CHARINDEX('.',TASK_NAME),LEN(TASK_NAME)))
WHERE TASK_UID=0
And WPROJ_ID in
(SELECT WPROJ_ID from MSP_WEB_PROJECTS
WHERE PROJ_ID= @nProjID
OR PROJ_ID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID))) )


--UPDATE MSP_VIEW_PROJ_PROJECTS_ENT
update msp_view_proj_projects_ent set projectenterprisename = '<>\' + @newName
where wproj_id in
(SELECT WPROJ_ID from MSP_WEB_PROJECTS
WHERE PROJ_ID= @nProjID
OR PROJ_ID in
(select PROJ_ID from MSP_PROJECTS
where PROJ_ID in (@nProjID)
OR (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME))) in
(select (SUBSTRING(PROJ_NAME,0,CHARINDEX('.',PROJ_NAME)))
from MSP_PROJECTS
where PROJ_ID in (@nProjID))) )

--UPDATE MSP_TASKS
update MSP_TASKS set TASK_NAME = @newName
where proj_id = @nProjID AND convert(varchar(100),task_outline_num) in ('0')

GO