SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Move a Stored Procedure to a New Schema–SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I needed to do recently was move an object. I was testing the WideWorldImporters database and created an object in the dbo schema. That’s the default for me, which is fine. However, in this case I wanted it in a different schema.

The way to do this is with the ALTER SCHEMA command. There is a TRANSFER option, which takes the original schema and object name.

In my case, I had the dbo.GetOpenPurchaseOrderCount procedure in my database.

2017-06-07 14_40_29-SQLQuery1.sql - (local)_SQL2016.WideWorldImporters-RR (PLATO_Steve (53))_ - Micr

I used this command to move it.

TRANSFER dbo.GetOpenPurchaseOrderCount

And then verified things moved.

2017-06-07 14_40_37-SQLQuery1.sql - (local)_SQL2016.WideWorldImporters-RR (PLATO_Steve (53))_ - Micr



This was one of those quick items where I checked the ALTER commands, thinking it was in there. I didn’t see a changeobjectschema procedure, and since this was a new skill, it was a 5 minute blog.

Filed under: Blog Tagged: security, SQLNewBlogger, syndicated, T-SQL

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...