CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqldatabase-schema
Filling The Stack is What I DO
Filling The Stack is What I DO

Reputation: 6741

How to BULK UPDATE SQL Schemas

I am attempting to update a specific schema for all the tables, procs, and views; however, I am receiving a permissions error and I've tried everything.

Can someone help show to the correct way to set the permissions for the query below to not receive the error below.

Query

    USE CAD

DECLARE @OldSchema VARCHAR(200)
DECLARE @NewSchema VARCHAR(200)
DECLARE @SQL nvarchar(4000)
SET @OldSchema = 'Storefront'
SET @NewSchema = 'CadType'

DECLARE tableCursor CURSOR FAST_FORWARD FOR 
    SELECT 'ALTER SCHEMA  ['+ @OldSchema +'] TRANSFER [' + @NewSchema + '].[' + DbObjects.Name + '];' AS Cmd
    FROM sys.Objects DbObjects
    INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
    WHERE SysSchemas.Name = @OldSchema
    AND (DbObjects.Type IN ('U', 'P', 'V'))
OPEN tableCursor 
FETCH NEXT FROM tableCursor INTO  @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @SQL
    EXEC (@SQL)
    FETCH NEXT FROM tableCursor INTO  @SQL
END
CLOSE tableCursor 
DEALLOCATE tableCursor 
PRINT '*** Finished ***'

ERROR

ALTER SCHEMA  [Storefront] TRANSFER [CadType].[proc_UpdateVertical];
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'proc_UpdateVertical', because it does not exist or you do not have permission.
ALTER SCHEMA  [Storefront] TRANSFER [CadType].[proc_UpdateElevationQty];
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'proc_UpdateElevationQty', because it does not exist or you do not have permission.
ALTER SCHEMA  [Storefront] TRANSFER [CadType].[proc_Update_Set_LeafIsActive];
Msg 15151, Level 16, State 1, Line 1

Upvotes: 0

Views: 503

Answers (1)

Serg
Serg

Reputation: 22811

From https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql :

ALTER SCHEMA schema_name TRANSFER [ entity_type :: ] securable_name [;]

Arguments

schema_name Is the name of a schema in the current database, into which the securable will be moved. Cannot be SYS or INFORMATION_SCHEMA.

securable_name Is the one-part or two-part name of a schema-contained securable to be moved into the schema.

So you need

SELECT 'ALTER SCHEMA  ['+ @NewSchema +'] TRANSFER [' + @OldSchema + '].[' + DbObjects.Name + '];' AS Cmd

Upvotes: 1

Related Questions