How could I script all schema-bound views (and other objects that prevent table truncate like "filtered indexes")?

How could I script all schema-bound views?

P.S. Parent problem is that schema-bound views (as well as filtered indexes) ruin SSMA (SQL SERVER Migration Assistant) data import, because SSMA has strange and not configurable behavior: call truncate table before import. Participating in schema bound views (indexed views) prevent table truncation, other such object: "filtered index".

Answers


Use SSMS functionality to Generate Script. In SSMS just right click on database, go to Task and than Generate Script that will give you option to choose, views tables and etc. Just pick what you want and you are done.

Read this link for details on steps

http://technet.microsoft.com/en-us/library/hh245282.aspx

You can use this for get scripts for all views from specific database and only from specific schema

Use [MyDatabase]
SELECT o.name
       ,s.name
       ,OBJECT_DEFINITION(o.object_id) AS Create_script
    FROM sys.objects AS o
    JOIN sys.schemas AS s
        ON o.schema_id = s.schema_id
    WHERE type_desc = 'View'
    AND s.name = 'dbo' --Replace with your schema

alter that just select everything from 3rd column and paste into notepad++ or other tool of your choice. Just save the file as .sql and your scripts are ready to go.


SMO / powershell solution:

[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)

Or the same idea packed into T-SQL strings for using with xp_cmdshell. As a result you will get new stored procedure ManageObjectsThatPreventTruncate ( name can be configurable in the script "header"). Check the content of it (there should be all views and filtered indexes scripted) and use it this way:

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

the script:

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

Need Your Help

TreeView with DataBinding

c# wpf data-binding mvvm

I want to create a databinding on a TreeView with the following model:

Correct syntax of a HTTP 100 Continue response

http rfc2616

For me, one of the weakest points of the HTTP 1.1 RFC and the various implementations around is how to deal with 100 Continue headers.