USP_DROPSCHEMABOUND

CREATE procedure dbo.USP_DROPSCHEMABOUND (@tables nvarchar(max))
as
begin

  declare @DROP nvarchar(max);
  declare @DEFINITION nvarchar(max);

  if not exists ( select name from sys.tables where name = 'SCHEMABOUNDVIEWSDROPPED') 
  begin
    CREATE TABLE [dbo].[SCHEMABOUNDVIEWSDROPPED](
      NAME nvarchar(max) not null
      ,DROPSTMT nvarchar(max) not null
        ,[DEFINITION] [nvarchar](max) NOT NULL
        ,DROPPED int
        ,[OBJECT_ID] int
        ,[PRIORITY] int
      ) ON [DEFGROUP]
  end;

  if not exists(select 1 from sys.tables where name = 'SCHEMABOUNDVIESINDEXESDROPPED')
  begin
    CREATE TABLE dbo.SCHEMABOUNDVIESINDEXESDROPPED (
        INDEXCOMMAND nvarchar(max) not null
        ,INDEXNAME nvarchar(max) not null
        ,[OBJECT_ID] int
    ) on [DEFGROUP]
  end;


with ObjectDepends( object_id, level, name) as (
select o.object_id, 0 level, o.name
from 
  sys.objects as o
  inner join sys.sql_dependencies d
    on o.object_id = d.object_id
  inner join sys.objects r
    on r.object_id = d.referenced_major_id
where
  d.class = 1
  and o.type = 'V'
  and d.referenced_minor_id = 0
  and r.name in (
        select 
          substring(','+@tables+',', NUM, charindex(',' ,','+@tables+',', NUM) - NUM)
          from dbo.NUMBERS
          where 
            NUM<=len(@tables)+2 and substring(',' + @tables + ',' ,NUM - 1, 1) = ',' 
            and charindex(',' , ',' + @tables + ',' , NUM) - NUM > 0
      )   
union all
 select o.object_id, level + 1, o.name
 from
  sys.objects as o
  inner join sys.sql_dependencies d
    on o.object_id = d.object_id
  inner join ObjectDepends r
    on r.object_id = d.referenced_major_id
where
  d.class = 1
  and o.type = 'V'
  and d.referenced_minor_id = 0
) 
insert into [SCHEMABOUNDVIEWSDROPPED] (NAME, DROPSTMT, DEFINITION, DROPPED, OBJECT_ID,PRIORITY)
    select 
  od.name
    ,replace(replace(m.definition, 'with schemabinding', ''), 'create view', 'alter view') as ObjName
    ,replace(m.definition, 'create view', 'alter view')
    ,0
    ,od.object_id
    ,MAX(od.level)
from ObjectDepends as od
inner join sys.sql_modules as m
  on od.object_id = m.object_id
left outer join SCHEMABOUNDVIEWSDROPPED AS S2
      on S2.NAME = od.NAME
where S2.NAME is null      
group by od.name, m.definition,od.object_id  
;


    WITH indexCTE AS
    (
        SELECT DISTINCT i.index_id, i.name, i.object_id, i.is_unique, i.filter_definition, ds.name as PartitionScheme
        FROM sys.indexes i 
        INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
        inner join sys.data_spaces as ds on ds.data_space_id=i.data_space_id
        WHERE EXISTS (SELECT * FROM sys.columns c WHERE c.column_id = ic.column_id AND c.object_id = ic.object_id)

    ), 
    indexCTE2 AS
    (
        SELECT            
          indexCTE.object_ID
            ,indexCTE.name 'IndexName' 
            ,OBJECT_NAME(indexCTE.object_ID) 'TableName'
            ,CASE indexCTE.index_id 
                WHEN 1 THEN 'CLUSTERED'
                ELSE 'NONCLUSTERED'
             END AS 'IndexType'
            ,CASE indexCTE.is_unique
                WHEN 1 THEN 'UNIQUE '
                ELSE ''
             END as 'UniqueType'
            ,(SELECT DISTINCT c.name + ','
             FROM sys.columns c 
             INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 0
             WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id 
             FOR XML PATH('')
            ) ixcols,
            ISNULL(
            (SELECT DISTINCT c.name + ','
             FROM sys.columns c 
             INNER JOIN sys.index_columns ic ON c.object_id = ic.object_id AND ic.column_id = c.column_id AND ic.Is_Included_Column = 1
             WHERE indexCTE.OBJECT_ID = ic.object_id AND indexCTE.index_id = ic.index_id 
             FOR XML PATH('')
            ), '') includedcols
            ,case when indexCTE.filter_definition is null then '' else ' WHERE ' + indexCTE.filter_definition end as filter_definition
            ,case when indexCTE.PartitionScheme is null then '' else ' ON ' + indexCTE.PartitionScheme end as PartitionScheme
        FROM indexCTE
    ) 
    insert into dbo.SCHEMABOUNDVIESINDEXESDROPPED (INDEXNAME, INDEXCOMMAND, OBJECT_ID)
    SELECT 
      IndexName,
        'CREATE ' + UniqueType + IndexType + ' INDEX ' + IndexName + ' ON ' + SCHEMABOUNDVIEWSDROPPED.NAME + 
        '(' + SUBSTRING(ixcols, 1, LEN(ixcols)-1) + 
        CASE LEN(includedcols)
            WHEN 0 THEN ')'
            ELSE ') INCLUDE (' + SUBSTRING(includedcols, 1, LEN(includedcols)-1) + ')'
        END +
        indexCTE2.filter_definition + 
        indexCTE2.PartitionScheme
        ,indexCTE2.OBJECT_ID
    FROM indexCTE2
    inner join dbo.SCHEMABOUNDVIEWSDROPPED on SCHEMABOUNDVIEWSDROPPED.NAME = indexCTE2.TableName
    order by indexCTE2.IndexType
;

 declare CMod cursor FAST_FORWARD for 
  select 
    SCHEMABOUNDVIEWSDROPPED.DROPSTMT
    ,SCHEMABOUNDVIEWSDROPPED.DEFINITION
  from [SCHEMABOUNDVIEWSDROPPED]     
  where 
    SCHEMABOUNDVIEWSDROPPED.DROPPED = 0
  order by [PRIORITY] DESC
  ;    


  open CMod;
  fetch next from CMod into @DROP, @DEFINITION;
  while @@FETCH_STATUS = 0
  begin

    exec (@DROP);
    fetch next from CMod into @DROP, @DEFINITION;
  end;
  close Cmod;
  deallocate CMod;

  update dbo.[SCHEMABOUNDVIEWSDROPPED] set DROPPED = 1 where DROPPED = 0;
end;