21

How do I switch off SCHEMABINDING for a view without recreating it?

11

Yes. It's good that you use SCHEMABINDING (we do always) and sometimes you have to remove it to change a dependent object. Just ALTER the view

ALTER VIEW myView
--Remove this WITH SCHEMABINDING
AS
SELECT ...
GO
  • so did I, but sometimes other objects (functions, views) depend on this one. So it will be good to mark /unmark this flag for a time :). So it is impossible in the current version of db, yes? – garik Mar 28 '11 at 16:43
  • @garik: correct, I have the same problem. Run ALTER on each dependent object... At any point in time SQL Server will enforce the rules: you can't "switch off" because this would lead to inconsistency – gbn Mar 28 '11 at 16:45
8

Won't ALTER VIEW allow for you to get this done? When you create a view you would do:

CREATE VIEW
WITH SCHEMABINDING
AS
SELECT stmt
GO

so, lose the WITH clause:

ALTER VIEW viewname
AS
SELECT stmt
GO

See ALTER VIEW on MSDN

4

After looking around for hours, I created 2 stored proc for this. Hope this helps someone

CREATE PROCEDURE ViewRemoveSchemaBinding
    @ViewName VARCHAR(MAX)
AS
BEGIN
    DECLARE @PositionShemaBinding INT
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = OBJECT_DEFINITION(OBJECT_ID(@ViewName));
    SET @PositionShemaBinding = CHARINDEX('WITH SCHEMABINDING', @Command)

    IF NOT @PositionShemaBinding = 0 BEGIN
        -- WITH SCHEMA BINDING IS PRESENT... Let's remove it !
        SET @Command = STUFF(@Command, CHARINDEX('WITH SCHEMABINDING', @Command), LEN('WITH SCHEMABINDING'), '');
        SET @Command = REPLACE(@Command, 'CREATE VIEW', 'ALTER VIEW');

        EXECUTE sp_executesql @Command
    END
END

And to put the SCHEMABINDING :

CREATE PROCEDURE ViewAddSchemaBinding
    @ViewName VARCHAR(MAX)
AS
BEGIN
    DECLARE @PositionShemaBinding INT
    DECLARE @Command NVARCHAR(MAX)
    DECLARE @ObjectName VARCHAR(MAX)

    SELECT  @Command = OBJECT_DEFINITION(OBJECT_ID(@ViewName)),
            @ObjectName = OBJECT_NAME(OBJECT_ID(@ViewName));

    SET @PositionShemaBinding = PATINDEX('%WITH SCHEMABINDING%', @Command)

    IF @PositionShemaBinding = 0 BEGIN
        -- WITH SCHEMA BINDING IS NOT PRESENT... Let's add it !
        SET @Command = REPLACE(@Command, 'CREATE VIEW', 'ALTER VIEW');

        -- IF OBJECT NAME IS INTO BRAKETS, We need to handle it
       IF NOT CHARINDEX('[' + @ObjectName + ']', @Command) = 0 BEGIN
           SET @ObjectName = '[' + @ObjectName + ']'
       END

       SET @Command = STUFF(@Command, CHARINDEX(@ObjectName, @Command), LEN(@ObjectName), @ObjectName + ' WITH SCHEMABINDING ');

        EXECUTE sp_executesql @Command
    END
END

It is provided "as is"...

2

This version of ViewRemoveSchemaBinding works even if the view has been renamed since it was created. (The problem is that if the view has been renamed, OBJECT_DEFINITION() will still return a definition using the old name.)

CREATE PROCEDURE [dbo].[ViewRemoveSchemaBinding]
    @ViewName VARCHAR(MAX)
AS
BEGIN
    DECLARE @PositionShemaBinding INT
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = OBJECT_DEFINITION(OBJECT_ID(@ViewName));
    SET @PositionShemaBinding = CHARINDEX('WITH SCHEMABINDING', @Command)

    IF NOT @PositionShemaBinding = 0 BEGIN
        SET @Command = 'ALTER VIEW ' + @ViewName + ' ' + RIGHT(@Command, LEN(@Command) - @PositionShemaBinding + 1);

        EXECUTE sp_executesql @Command
    END
END

It seems that after running this the renaming issue goes away, and so ViewAddSchemaBinding doesn't need to be altered....

  • 1
    This doesn't work, as the command still contains 'WITH SCHEMABINDING' - to fix it, change the usage of RIGHT to: RIGHT(@Command, LEN(@Command) - (@PositionShemaBinding + LEN('WITH SCHEMABINDING'))) – Cocowalla Jan 23 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.