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:
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.
