August 1, 2005 at 1:24 pm
I have a problem renaming the column thru the script I wrote. If I replace the last statement for sp_rename with the real names like bellow then works:
exec sp_rename 'id_test.new_ID', 'old_identuty', 'COLUMN'
I also checked with Select id_test.new_ID from id_test - and it returns value.
Please, advise what wron I'm doing?
This is the error I'm getting:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
Thanks a lot,mj
use tempdb
GO
--drop table id_test
create table id_test (old_identuty int identity,col1 varchar, col2 varchar)
--Run the procedure to find out what is the Identity column name you want to replace
sp_help id_test--type you table name
-- DECLARE user input variables
DECLARE @tablename varchar(100)
DECLARE @Old_id_name varchar(100)
--DECLARE @new_id_name varchar(100)
select @tablename='id_test'--'Type your table name between the quotes - no spaces'
select @Old_id_name = 'old_identuty'--'Paste the old identity between the quotes - no spaces'
--select @new_id_name = 'enter your table new id'
-- DECLARE global variables
DECLARE @ALTERTABLESTM NVARCHAR(4000)
DECLARE @UPDATESTM NVARCHAR(4000)
DECLARE @ALTERTABLESTM2 NVARCHAR(4000)
DECLARE @ALTERTABLESTM3 NVARCHAR(4000)
DECLARE @RENAMESTM1 VARCHAR(500)
DECLARE @RENAMESTM2 VARCHAR(500)
DECLARE @tableobjNID varchar (100)
DECLARE @tableobjID varchar (100)
DECLARE @test-2 varchar (100)
SET @tableobjNID = ''''+@tablename+'.new_ID'''
print @tableobjNID
SET @tableobjID = ''''+@tablename+'.'+@Old_id_name
print @tableobjID
--Create a new column to hold the new ID
Set @ALTERTABLESTM = N'ALTER TABLE '+ @tablename + N' ADD new_ID int null'
print @ALTERTABLESTM
EXEC SP_EXECUTESQL @ALTERTABLESTM
--Update the new column with the values of the old ID
--Set @UPDATESTM = N'update '+ @tablename + N' set new_ID = id'
Set @UPDATESTM = N'update '+ @tablename + N' set new_ID = '+@Old_id_name
print @UPDATESTM
EXEC SP_EXECUTESQL @UPDATESTM
--Make the new column mandatory - add NOT NULL constraint
Set @ALTERTABLESTM2 = N'alter table '+ @tablename + N' alter column new_ID int not null'
print @ALTERTABLESTM2
EXEC SP_EXECUTESQL @ALTERTABLESTM2
--Drop the old id column
Set @ALTERTABLESTM3 = N'alter table '+ @tablename + N' drop column '+@Old_id_name
print @ALTERTABLESTM3
EXEC SP_EXECUTESQL @ALTERTABLESTM3
--Rename the new column to the name of the old one
Set @RENAMESTM1 = @tableobjNID
Set @RENAMESTM2 = ''''+@Old_id_name+''''
print @RENAMESTM1
print @RENAMESTM2
exec sp_rename @RENAMESTM1, @RENAMESTM2, 'COLUMN'
August 1, 2005 at 2:08 pm
Never mind - I fix it - hate quates - don't you too?
Here is the correct script if somebody wants to use it:
-- DECLARE user input variables
DECLARE @tablename varchar(100)
DECLARE @Old_id_name varchar(100)
--DECLARE @new_id_name varchar(100)
select @tablename='id_test'--'Type your table name between the quotes - no spaces'
select @Old_id_name = 'old_identuty'--'Paste the old identity between the quotes - no spaces'
--select @new_id_name = 'enter your table new id'
-- DECLARE global variables
DECLARE @ALTERTABLESTM NVARCHAR(4000)
DECLARE @UPDATESTM NVARCHAR(4000)
DECLARE @ALTERTABLESTM2 NVARCHAR(4000)
DECLARE @ALTERTABLESTM3 NVARCHAR(4000)
DECLARE @RENAMESTM1 VARCHAR(500)
DECLARE @RENAMESTM2 VARCHAR(500)
DECLARE @tableobjNID varchar (100)
DECLARE @tableobjID varchar (100)
DECLARE @test-2 varchar (100)
SET @tableobjNID = ''''+@tablename+'.new_ID'''
print '@tableobjNID = '+@tableobjNID
SET @tableobjID = ''''+@tablename+'.'+@Old_id_name
print '@tableobjID = '+@tableobjID
--Create a new column to hold the new ID
Set @ALTERTABLESTM = N'ALTER TABLE '+ @tablename + N' ADD new_ID int null'
print '@ALTERTABLESTM = '+@ALTERTABLESTM
EXEC SP_EXECUTESQL @ALTERTABLESTM
--Update the new column with the values of the old ID
--Set @UPDATESTM = N'update '+ @tablename + N' set new_ID = id'
Set @UPDATESTM = N'update '+ @tablename + N' set new_ID = '+@Old_id_name
print '@UPDATESTM = '+@UPDATESTM
EXEC SP_EXECUTESQL @UPDATESTM
--Make the new column mandatory - add NOT NULL constraint
Set @ALTERTABLESTM2 = N'alter table '+ @tablename + N' alter column new_ID int not null'
print '@ALTERTABLESTM2 = '+@ALTERTABLESTM2
EXEC SP_EXECUTESQL @ALTERTABLESTM2
--Drop the old id column
Set @ALTERTABLESTM3 = N'alter table '+ @tablename + N' drop column '+@Old_id_name
print '@ALTERTABLESTM3 = '+@ALTERTABLESTM3
EXEC SP_EXECUTESQL @ALTERTABLESTM3
--Rename the new column to the name of the old one
--Set @RENAMESTM1 = @tableobjNID
Set @RENAMESTM1 = @tablename+'.new_ID'
--Set @RENAMESTM2 = ''''+@Old_id_name+''''
Set @RENAMESTM2 = @Old_id_name
print '@RENAMESTM1 = '+@RENAMESTM1
print '@RENAMESTM2 = '+@RENAMESTM2
exec sp_rename @RENAMESTM1, @RENAMESTM2, 'COLUMN'
--exec sp_rename 'id_test.new_ID', 'old_identuty', 'COLUMN'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply