Hi Alex,
Thanks for a very useful product.
I had to add support for schema bound views so am contributing the sql code template I created below for you.
Note that rather than drop the views and encounter dependency issues, I simply remove the schema binding
from views by generating ALTER statements. The segment has to be executed after indexes are dropped in case the view has indices. I've named both scripts so that they sort into the correct position although it does mean the numbering is at odds with your original. To make any further additions easier perhaps you could consider 4 digit numbers for any update you create.
Regards
Alan Nicholas
0111 Disable Schema binding.2005.sql
<br />
declare @name sysname,<br />
@id int,<br />
@id_last int,<br />
@last sysname,<br />
@owner sysname,<br />
@test_text nvarchar(4000),<br />
@pos_create_fn int,<br />
@pos_comment_start int,<br />
@pos_comment_end int,<br />
@offset int,<br />
@pos_function_name int,<br />
@pos_object_name int,<br />
@c cursor,<br />
@sql_segment nvarchar(4000),<br />
@text_ptr binary(16),<br />
@ansi_nulls nvarchar(100)<br />
<br />
set @id_last =0<br />
<br />
set @C = cursor for<br />
select o.name as functionName, <br />
o.id,<br />
sc.text,<br />
u.name,<br />
'SET ANSI_NULLS ' + case when OBJECTPROPERTY(o.id,'IsAnsiNullsOn') =1 then 'ON' else 'OFF' end as [AnsiNulls]<br />
from sysobjects o<br />
join syscomments sc<br />
on o.id = sc.id<br />
join sysusers u<br />
on u.uid = o.uid<br />
where objectproperty(o.id,'IsView') =1<br />
and objectproperty(o.id,'IsSchemaBound')=1 <br />
and objectproperty(o.id,'IsMSShipped')=0 <br />
order by o.id, <br />
sc.colid <br />
<br />
<br />
<br />
open @C<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
while @@Fetch_Status=0<br />
begin<br />
if @id<>@id_last<br />
begin<br />
--add the ansi nulls setting<br />
insert into #sql (sql) values (@ansi_nulls)<br />
<br />
--add a row for our data<br />
insert into #sql (sql) values ('')<br />
<br />
--get a text pointer<br />
SELECT @text_ptr = TEXTPTR(sql) FROM #sql where id = (select max(id) from #sql)<br />
<br />
set @id_last =@id<br />
<br />
set @pos_create_fn = patindex('%with%schemabinding%', @sql_segment collate latin1_general_ci_ai)<br />
while @pos_create_fn>0<br />
begin<br />
set @pos_comment_end = charindex('schemabinding',@sql_segment,@pos_create_fn) + len('schemabinding')<br />
set @sql_segment = stuff(@sql_segment,@pos_create_fn,@pos_comment_end-@pos_create_fn,' ')<br />
set @pos_create_fn = patindex('%with%schemabinding%', @sql_segment collate latin1_general_ci_ai)<br />
end<br />
<br />
set @pos_create_fn = patindex('%create%view%', @sql_segment collate latin1_general_ci_ai)<br />
while @pos_create_fn>0<br />
begin<br />
set @pos_comment_end = charindex('view',@sql_segment,@pos_create_fn) + len('view')<br />
set @sql_segment = stuff(@sql_segment,@pos_create_fn,@pos_comment_end-@pos_create_fn,'alter view')<br />
set @pos_create_fn = patindex('%create%view%', @sql_segment collate latin1_general_ci_ai)<br />
end<br />
<br />
end<br />
<br />
UPDATETEXT #sql.sql @text_ptr NULL 0 @sql_segment<br />
<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
end<br />
Close @C<br />
deallocate @C<br />
2011 Enable Schema binding.2005.sql
<br />
declare @name sysname,<br />
@id int,<br />
@id_last int,<br />
@last sysname,<br />
@owner sysname,<br />
@test_text nvarchar(4000),<br />
@pos_create_fn int,<br />
@pos_comment_start int,<br />
@pos_comment_end int,<br />
@offset int,<br />
@pos_function_name int,<br />
@pos_object_name int,<br />
@c cursor,<br />
@sql_segment nvarchar(4000),<br />
@text_ptr binary(16),<br />
@ansi_nulls nvarchar(100)<br />
<br />
set @id_last =0<br />
<br />
set @C = cursor for<br />
select o.name as functionName, <br />
o.id,<br />
sc.text,<br />
u.name,<br />
'SET ANSI_NULLS ' + case when OBJECTPROPERTY(o.id,'IsAnsiNullsOn') =1 then 'ON' else 'OFF' end as [AnsiNulls]<br />
from sysobjects o<br />
join syscomments sc<br />
on o.id = sc.id<br />
join sysusers u<br />
on u.uid = o.uid<br />
where objectproperty(o.id,'IsView') =1<br />
and objectproperty(o.id,'IsSchemaBound')=1 <br />
and objectproperty(o.id,'IsMSShipped')=0 <br />
order by o.id, <br />
sc.colid <br />
<br />
<br />
open @C<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
while @@Fetch_Status=0<br />
begin<br />
if @id<>@id_last<br />
begin<br />
--add the ansi nulls setting<br />
insert into #sql (sql) values (@ansi_nulls)<br />
<br />
--add a row for our data<br />
set @sql_segment = replace(@sql_segment,'CREATE ' COLLATE Latin1_General_CS_AS ,'ALTER ')<br />
insert into #sql (sql) values (@sql_segment)<br />
end<br />
<br />
fetch next from @C into @name, @id, @sql_segment, @owner, @ansi_nulls<br />
end<br />
Close @C<br />
deallocate @C<br />
|