Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Converting Views to Schema Binding

Discussion in 'General' started by telmessos, Oct 1, 2012.

  1. telmessos

    telmessos New Member

    Hi All,

    I have a SQL database which has many views and needs indexing. Unfortunately SQL server does not let me add indexes with schema bind error. There are app. 50 views created on the database and I don't want to delete and re-create them one by one.

    Is there an easier way to convert regular views to schemabind views to save time and effort?

    Many thanks
    telmessos
  2. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    Hi there, and welcome to MSSQLForum.com :)

    Off the bat, I do not think what you want is possible, but a simple script could probably do the trick. You could query sys.all_views in the database for a list of all available views, and then use a WHILE loop to grab the view definition from the text column of the table dbo.syscomments. Something along the lines of the following, but then using the WHILE loop to fill in @view_name and the object_id for the view:

    Code (text):

    DECLARE @view_name sysname;
    DECLARE @view_definition nvarchar(max);
    DECLARE @SQL nvarchar(max);
    DECLARE @i int;

    SELECT @view_name = '<view_name>';
    SELECT @view_definition = text FROM dbo.syscomments WHERE id = <object_id>;

    SELECT @SQL = 'DROP VIEW ' + @view_name;
    EXEC sp_executesql @SQL;

    SELECT @i = PATINDEX('%AS%', @view_definition);

    SELECT @SQL =
        LEFT(@view_definition, @i - 2) + ' WITH SCHEMABINDING ' +
        SUBSTRING(@view_definition, @i, LEN(@view_definition) - @i);

    EXEC sp_executesql @SQL;
     
    In short, the script will grab the current definition of the view, drop it, and then add 'WITH SCHEMABINDING' right before the ' AS ' part of the definition and recreate the view afterwards.

    Ensure you run this on a test system first and verify it works as intended. You might run into an issue if your view names include 'AS', as it might throw off the PATINDEX line.

    Please note that it generally is not recommended to directly work against the system tables. The script above is provided as is, without warranty of any kind, either expressed or implied.

Share This Page