すべてのスキーマバインドビュー（および「フィルター処理されたインデックス」のようなテーブルの切り捨てを防ぐ他のオブジェクト）をスクリプト化するにはどうすればよいですか？

SMO / PowerShellソリューション：

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null; 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended')|Out-Null; 
$sr = '<server>'
$ln = '<login>'
$pw = '<password>'
$db = '<database>'

$u = 'Microsoft.SqlServer.Management.Smo'
$n = [Environment]::NewLine;
$s = new-object ($u+'.Server') $sr;
$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw;
$s.SetDefaultInitFields((new-object ($u+'.View')).getType(), "IsSchemaBound");
$v=$s.Databases[$db].Views|Where-Object{$_.IsSchemaBound -eq $true}
if ($v | where $_.Triggers.Count -gt 0) {throw new-object System.ArgumentException 'Views with triggers are not supported'}
$_createScript = ('SET ARITHABORT ON','SET CONCAT_NULL_YIELDS_NULL ON','SET QUOTED_IDENTIFIER ON','SET ANSI_NULLS ON','SET ANSI_PADDING ON','SET ANSI_WARNINGS ON','SET NUMERIC_ROUNDABORT OFF')+
     ($v | %{$i=$_;(@()+'GO'+($i.Script()|where {$_ -notlike "SET * ON"})+'GO'+ ($i.Indexes|sort IsClustered –desc|%{$_.Script()}| where {$_ -notlike "SET *"}))})
$_dropScript =  $v | %{$_.Script(( new-object ($u+'.ScriptingOptions') -prop @{ScriptDrops =$true}))}
Write-Host (@()+'/*'+$_dropScript+'*/'+('-' * 40)+ $_createScript -join $n)

または、xp_cmdshellで使用するためにT-SQL文字列にパックされた同じアイデア。その結果、新しいストアドプロシージャManageObjectsThatPreventTruncateを取得します（名前はスクリプト「ヘッダー」で構成できます）。その内容を確認し（すべてのビューとフィルター処理されたインデックスがスクリプト化されている必要があります）、次のように使用します。

ManageObjectsThatPreventTruncate(@drop=1) -- drop all indexed views and filtered indexes
ManageObjectsThatPreventTruncate() -- crate all indexed views and filtered indexes

ManageObjectsThatPreventTruncate(@drop=1, @schema='x') -- drop all indexed views and filtered indexes from schema X
ManageObjectsThatPreventTruncate(@schema='x') -- crate all indexed views and filtered indexes from schema X

ManageObjectsThatPreventTruncate(@drop=1, @schema='x', @name='y') -- drop concreate indexed view X.Y or drop all filtered indexes from table X.Y
ManageObjectsThatPreventTruncate(@schema='x', @name='y') -- crate concreate indexed view X,Y or drop all filtered indexes from table X.Y

ManageObjectsThatPreventTruncate(@drop=1, @schema='x', @name='y', @indexName='z') -- drop concreate filtered index X.Y
ManageObjectsThatPreventTruncate(@schema='x', @name='y', @indexName='z') -- crate concreate filtered index X.Y

スクリプト：

DECLARE @OUTPUT TABLE (id int identity(1,1), line nvarchar(max))
DECLARE @cmd VARCHAR(8000), @psAlterSP VARCHAR(8000),  @psGetDatabase VARCHAR(8000), @psGetViews VARCHAR(8000), @psGetFilteredIndexes VARCHAR(8000), @psLoadAssemblies VARCHAR(8000), @script nvarchar(max) ='',
    @psCompose1 VARCHAR(8000), @psCompose2 VARCHAR(8000), @psCompose3 VARCHAR(8000), @psCompose4 VARCHAR(8000), @psCompose5 VARCHAR(8000), @psComposeAll VARCHAR(8000)
DECLARE @sr nvarchar(max)='<server>', 
        @ln nvarchar(max)='<loginname>', 
        @pw nvarchar(max)='<password>', 
        @db nvarchar(max)=DB_NAME(),
        @sp sysname = 'ManageObjectsThatPreventTruncate',
        @ss sysname = 'dbo'
SET @psLoadAssemblies  = '[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'')|Out-Null;$ErrorActionPreference = ''Stop'';'
SET @psGetDatabase='$u =''Microsoft.SqlServer.Management.Smo'';$n = [Environment]::NewLine;$a = ''''+[char]39;$s = new-object ($u+''.Server'') $sr;$c = $s.ConnectionContext;$c.LoginSecure=$false;$c.Login=$ln;$c.Password=$pw;$s.SetDefaultInitFields((new-object ($u+''.View'')).getType(),''IsSchemaBound'');$d = $s.Databases[$db];'
SET @psGetDatabase=REPLACE(@psGetDatabase,'$sr',''''+@sr+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$ln',''''+@ln+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$pw',''''+@pw+'''')
SET @psGetDatabase=REPLACE(@psGetDatabase,'$db',''''+@db+'''')
SET @psGetViews = '$v=$d.Views|where {$_.IsSchemaBound -eq $true};if ($v | where {$_.Triggers.Count -gt 0}) {throw new-object System.ArgumentException ''Views with triggers are not supported''};'
SET @psGetFilteredIndexes = '$r = $d.ExecuteWithResults(''SELECT object_id, Name FROM sys.indexes WHERE has_filter=1 ORDER BY object_id, Name'');$x = @();foreach ($i in $r.Tables[0].Rows) {$x+=$d.Tables.ItemById($i[0]).Indexes[$i[1]]};'
SET @psCompose1='$o1=(''BEGIN TRY'',''IF @drop=1'',''BEGIN'')+($v | %{$_s=$_.Schema;$_n=$_.Name; \"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_n'') DROP VIEW [$_s].[$_n]\"})+($x | %{$_s=$_.Parent.Schema;$_tn=$_.Parent.Name;$_in=$_.Name; \"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_tn'') and (@indexName is null or @indexName=''$_in'') DROP INDEX [$_in] ON [$_s].[$_tn]\"})+''END'';'
SET @psCompose2='$o2=(''ELSE'',''BEGIN'',''SET ARITHABORT ON'',''SET CONCAT_NULL_YIELDS_NULL ON'',''SET QUOTED_IDENTIFIER ON'',''SET ANSI_NULLS ON'',''SET ANSI_PADDING ON'',''SET ANSI_WARNINGS ON'',''SET NUMERIC_ROUNDABORT OFF'',''DECLARE @sql varchar(max)'');'
SET @psCompose3='$o3= ($v | %{$i=$_;$_s=$_.Schema;$_n=$_.Name; (@()+(\"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_n'')\")+''BEGIN''+(''SET @sql =''+$a+(($i.Script()|where {$_ -notlike ''SET * ON''}) -join '''').Replace($a,$a+$a)+$a)  +''exec (@sql)''+( $i.Indexes| sort IsClustered –desc| %{ @()+$n+(''SET @sql =''+$a+(($_.Script()| where {$_ -notlike ''SET *''}) -join '''').Replace($a,$a+$a) +$a) +''exec (@sql)''})+''END'' )});'
SET @psCompose4='$o4= ($x | %{$i=$_;$_s=$_.Parent.Schema;$_tn=$_.Parent.Name;$_in=$_.Name;(@()+(\"IF (@schema is null or @schema=''$_s'') and (@name is null or @name=''$_tn'') and (@indexName is null or @indexName=''$_in'')\")+''BEGIN''+(''SET @sql =''+$a+(($i.Script()|where {$_ -notlike ''SET * ON''}) -join '''').Replace($a,$a+$a)+$a)+''exec (@sql)''+''END'')});'
SET @psCompose5='$o5=(''END'',''END TRY'',''BEGIN CATCH'',''THROW'',''END CATCH'');'
SET @psComposeAll= '$o=($o1+$o2+$o3+$o4+$o5) -join $n;'

SET @psAlterSP = '$p = $d.StoredProcedures[\"$sp\",\"$ss\"]; if($p -eq $null){$p = new-object ($u+''.StoredProcedure'') $d, \"$sp\",\"$ss\";$p.TextHeader=\"CREATE PROCEDURE [$ss].[$sp]$n @drop bit = 0,$n @schema sysname = NULL,$n @name sysname = NULL,$n @indexName sysname=NULL $n AS$n\";$p.TextBody=$o;$p.Create()}else{$p[0].TextBody=$o; $p[0].Alter()}'
SET @psAlterSP=REPLACE(@psAlterSP,'$sp',@sp)
SET @psAlterSP=REPLACE(@psAlterSP,'$ss',@ss)

SET @cmd = 'powershell -Command "'+@psLoadAssemblies+@psGetDatabase+@psGetViews+@psGetFilteredIndexes+@psCompose1+@psCompose2+@psCompose3+@psCompose4++@psCompose5+@psComposeAll+@psAlterSP+'"'
PRINT LEN(@cmd)
DECLARE @result int
INSERT INTO @OUTPUT (line)
exec @result =xp_cmdshell @cmd

IF @result=0 AND (SELECT count(*) FROM @OUTPUT WHERE line is not null)=0
BEGIN
    PRINT 'OK'
END
ELSE
BEGIN
    PRINT 'FAILURE'
    SELECT @script+line FROM @OUTPUT
    WHERE line is not null
    ORDER BY id;
    THROW 50000,'Error during xp_cmdshell execution',1;
END