Change SCHEMABINDING for existing VIEW

Question

I have an existing database (SSDT project as source) where I have to update a table in. I'm only changing a column type from nvarchar(MAX) to nvachar(256). The problem is, that I have an existing view bound to this table with SCHEMABINDING. I'm not able to edit table column because SCHEMABINDING prevent changes which influences the VIEW.

The following script is executed in the PreDeploymentScript. When I call the DROP VIEW [base].[VIEW_DEPENDING_ON_TABLE] statement, the view is missing after deployment. My idea is, to disable SCHEMABINDING during deployment and enable it after finished. How is this possible in TSQL script? Or is there a better way to do this?

IF EXISTS ( SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = N'base'
        AND TABLE_NAME = 'TABLENAME'
        AND COLUMN_NAME = 'Instance'
        AND CHARACTER_MAXIMUM_LENGTH = -1)
AND NOT EXISTS (SELECT * FROM sys.indexes WHERE name='IX_TABLENAME_Instance' AND object_id = OBJECT_ID(N'[base].[TABLENAME]'))
BEGIN

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[base].[VIEW_DEPENDING_ON_TABLE]') AND type in (N'V'))
    BEGIN
        DROP VIEW [base].[VIEW_DEPENDING_ON_TABLE]
    END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[base].[TABLENAME]') AND type in (N'U'))
    BEGIN
        UPDATE [base].[TABLENAME] SET [Instance] = LEFT(Instance, 256)
        ALTER TABLE [base].[TABLENAME] ALTER COLUMN [Instance] NVARCHAR(256)
    END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[base].[TABLENAME]') AND type in (N'U'))
    BEGIN
        CREATE NONCLUSTERED INDEX [IX_TABLENAME_Instance] ON [base].[TABLENAME]
        (
            [Instance] ASC
        ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    END
END
GO

Externality

You don't want to drop the view, you just want to remove the schemabinding so you can make your table change. So ALTER VIEW dbo.name AS ..., then make your change, then ALTER VIEW dbo.name WITH SCHEMABINDING AS ... – Farmhand

@AaronBertrand Ok, but what to do in the first ALTER VIEW dbo.name AS ...? How to disable SCHEMABINDING? – Externality

Like I said, you alter the view but you leave out the WITH SCHEMABINDING option. There is no command to turn it off, you just change the view and don't turn it on. – Farmhand

Ok I understand, but the view definition is stored in another TSQL script. – Externality

Doesn't the view already exist? If so, then the view definition is also stored in sys.sql_modules, so you could access it programmatically. But you could also consider copying the view definition into this script and removing the schemabinding option yourself. Or you could call that other T-SQL script after deployment. – Farmhand

Perfect, took the definition from sys.sql_modules. Could you post this as answer? Thank you! – Externality

Answer 1

There is no command to disable WITH SCHEMABINDING. You do this either by dropping the view and re-creating it without the option, or simply altering it. In your case:

ALTER VIEW [base].[VIEW_DEPENDING_ON_TABLE]
-- WITH SCHEMABINDING
AS
    SELECT ...;

Once you make the change to the underlying table, you can alter the view again:

ALTER VIEW [base].[VIEW_DEPENDING_ON_TABLE]
WITH SCHEMABINDING
AS
    SELECT ...;

Don't have the script handy? That's ok, you can pull it from sys.sql_modules, but it will require some massaging to convert from CREATE to ALTER and to remove WITH SCHEMABINDING. You could write code that parses it, but that is very brittle, since replacing or commenting the phrase "with schemabinding" could be very difficult - it could have more than once space, tabs, carriage returns, non-printing characters, etc., and it could also exist in other parts of the code (like a comment or even as a table or column alias).

Farmhand

Why jump through all the hoops pulling the definition out of sys.sql_modules when that code has already been written in the form of Script View As… Alter in SSMS? – Wilie

@Ben it’s what the OP wanted. My answer wasn’t showing an ALTER that came from SSMS, it was the end result of my suggestion, whether the source came from the other T-SQL script, SSMS, or pulling from metadata. The replacement nonsense is only necessary if pulling from metadata. I assume the deployment is automated so that’s what that’s for... – Farmhand

I'm all for automation. But if the cost is much higher than the benefit (as would seem to be the case here for a one time alteration of the underlying table) I usually side against. But I get where you're coming from. – Wilie

@Ben I tried to push for simple manual adjustment in the comments above, but the OP seemed ecstatic when they found the definition in sys.sql_modules, so ¯\_(ツ)_/¯... – Farmhand