May 9, 2017 at 9:52 am
The following works well as it is...( if you enter the name of the object without the schema name in front of it (dbo.xxx ) )
It l work if the object is in the dbo schema.
NOW THEN
I tried the script with
Select @ObjName = 'dbo.spUpdateHOFHRP' Then it renames the object as dbo.dbo.spUpdateHOFHRP_bk_20170509
This is bad!
How can we modify the script below to accept a object in any schema and after renaming I wasnt it to look like 'schema_name.object_name' ( and not schema_name.schema_name.object_name )
Declare @Today char(8)
Select @Today=CONVERT(CHAR(8), GETDATE(), 112 );
Declare @ObjName Varchar(500)
Declare @NewName Varchar(500)
Declare @ObjExtension Varchar(500)
Select @ObjName = 'spUpdateHOFHRP'
Select @ObjExtension= '_bk_' + @Today
Select @NewName = @ObjName + @ObjExtension;
If object_id(@ObjName) IS NOT NULL
Begin
EXEC sp_rename @objname=@ObjName,@newname=@NewName, @objtype=NULL
PRINT 'Object ' + @ObjName + ' renamed to ' + @ObjName + @ObjExtension
End
[/code]
May 9, 2017 at 9:59 am
sp_rename only renames the object itself, you can't change the schema it is in, so when you specify the @newname, don't put the schema name in that. The documentation explicitly says @newname can only be a 1 part name:
https://technet.microsoft.com/en-us/library/ms188351(v=sql.110).aspx#Anchor_1
May 9, 2017 at 10:05 am
Chris Harshman - Tuesday, May 9, 2017 9:59 AMsp_rename only renames the object itself, you can't change the schema it is in, so when you specify the @newname, don't put the schema name in that. The documentation explicitly says @newname can only be a 1 part name:
https://technet.microsoft.com/en-us/library/ms188351(v=sql.110).aspx#Anchor_1
Problem: Sometimes I have a sp in the dob schema and another copy of the same sp in the provider schema.. Now I may want to rename the one in the provider schema. So how can i do that ?
May 9, 2017 at 10:10 am
you can specify the schema name of the original object in the @objname parameter, you just cannot specify a schema name in the @newname parameter.EXEC sp_renane @objname = 'provider.SPname', 'SPnewname';
May 9, 2017 at 10:48 am
Chris Harshman - Tuesday, May 9, 2017 10:10 AMyou can specify the schema name of the original object in the @objname parameter, you just cannot specify a schema name in the @newname parameter.EXEC sp_renane @objname = 'provider.SPname', 'SPnewname';
Cool, Thx
May 9, 2017 at 11:03 am
you are probably looking to move an object to the other schema. THERE ALTER SCHEMA command lets you move a schemaname.object name into a specific schema.
if i "develop" in the "developer" schema, and am ready to move the proc to the [dbo] or [Claims] or [Finance] schemas, it looks like this
ALTER SCHEMA [dbo] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Claims] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Finance] TRANSFER [Developer].[MyProcedure]
Lowell
May 9, 2017 at 11:27 am
Lowell - Tuesday, May 9, 2017 11:03 AMyou are probably looking to move an object to the other schema. THERE ALTER SCHEMA command lets you move a schemaname.object name into a specific schema.if i "develop" in the "developer" schema, and am ready to move the proc to the [dbo] or [Claims] or [Finance] schemas, it looks like this
ALTER SCHEMA [dbo] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Claims] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Finance] TRANSFER [Developer].[MyProcedure]
No, but thanks!
Our manager says when code needs to be moved to production he wants to first backup the existing sp and then add the new one instead of overwriting a the existing sp. So that if some something went wrong, we can always revert to the old sp.
May 9, 2017 at 9:09 pm
mw112009 - Tuesday, May 9, 2017 11:27 AMLowell - Tuesday, May 9, 2017 11:03 AMyou are probably looking to move an object to the other schema. THERE ALTER SCHEMA command lets you move a schemaname.object name into a specific schema.if i "develop" in the "developer" schema, and am ready to move the proc to the [dbo] or [Claims] or [Finance] schemas, it looks like this
ALTER SCHEMA [dbo] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Claims] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Finance] TRANSFER [Developer].[MyProcedure]No, but thanks!
Our manager says when code needs to be moved to production he wants to first backup the existing sp and then add the new one instead of overwriting a the existing sp. So that if some something went wrong, we can always revert to the old sp.
MUCH better to use a good source control app for that.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2017 at 9:19 pm
Jeff Moden - Tuesday, May 9, 2017 9:09 PMmw112009 - Tuesday, May 9, 2017 11:27 AMLowell - Tuesday, May 9, 2017 11:03 AMyou are probably looking to move an object to the other schema. THERE ALTER SCHEMA command lets you move a schemaname.object name into a specific schema.if i "develop" in the "developer" schema, and am ready to move the proc to the [dbo] or [Claims] or [Finance] schemas, it looks like this
ALTER SCHEMA [dbo] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Claims] TRANSFER [Developer].[MyProcedure]
ALTER SCHEMA [Finance] TRANSFER [Developer].[MyProcedure]No, but thanks!
Our manager says when code needs to be moved to production he wants to first backup the existing sp and then add the new one instead of overwriting a the existing sp. So that if some something went wrong, we can always revert to the old sp.MUCH better to use a good source control app for that.
Agreed. Without one, where would you backup the original procedure? Is the backup going to be dated? Where would it be stored? Is the storage location backed up? How many revisions are you going to keep? There's a lot to consider in there and, like Jeff pointed out, a source control application is designed to handle a lot of it for you. If it's repository is in a SQL Server database, you can back it up with the rest of your databases, have it covered by your DR plan, etc.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply