z9NUAN99

1public string ScriptDatabase()
2{
3      var sb = new StringBuilder();
4
5      var server = new Server(@"ServerName");
6      var databse = server.Databases["DatabaseName"];
7
8      var scripter = new Scripter(server);
9      scripter.Options.ScriptDrops = false;
10      scripter.Options.WithDependencies = true;
11      scripter.Options.IncludeHeaders = true;
12      //And so on ....
13
14
15      var smoObjects = new Urn[1];
16      foreach (Table t in databse.Tables)
17      {
18          smoObjects[0] = t.Urn;
19          if (t.IsSystemObject == false)
20          {
21              StringCollection sc = scripter.Script(smoObjects);
22
23              foreach (var st in sc)
24              {
25                  sb.Append(st);
26              }
27           }
28       }
29            return sb.ToString();
30 }
31	
32function SQL-Script-Database
33{
34    <#
35    .SYNOPSIS
36    Script all database objects for the given database.
37
38    .DESCRIPTION
39    This  function scripts all database objects  (i.e.: tables,  views, stored
40    procedures,  and user defined functions) for the specified database on the
41    the given serverinstance. It creates a subdirectory per object type under 
42    the path specified.
43
44    .PARAMETER savePath
45    The root path where to save object definitions.
46
47    .PARAMETER database
48    The database to script (default = $global:DatabaseName)
49
50    .PARAMETER DatabaseServer 
51    The database server to be used (default: $global:DatabaseServer).
52
53    .PARAMETER InstanceName 
54    The instance name to be used (default: $global:InstanceName).
55
56    .EXAMPLE
57    SQL-Script-Database c:temp AOIDB
58    #>
59
60    param (
61        [parameter(Mandatory = $true)][string] $savePath,
62        [parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
63        [parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer,
64        [parameter(Mandatory = $false)][string] $InstanceName = $global:InstanceName
65    )
66
67    try
68    {
69        if (!$DatabaseServer -or !$InstanceName)
70            { throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }
71
72        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName
73
74        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
75
76        $s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
77        $db = $s.databases[$database]
78
79        $objects = $db.Tables
80        $objects += $db.Views
81        $objects += $db.StoredProcedures
82        $objects += $db.UserDefinedFunctions
83
84        $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
85
86        $scripter.Options.AnsiFile = $true
87        $scripter.Options.IncludeHeaders = $false
88        $scripter.Options.ScriptOwner = $false
89        $scripter.Options.AppendToFile = $false
90        $scripter.Options.AllowSystemobjects = $false
91        $scripter.Options.ScriptDrops = $false
92        $scripter.Options.WithDependencies = $false
93        $scripter.Options.SchemaQualify = $false
94        $scripter.Options.SchemaQualifyForeignKeysReferences = $false
95        $scripter.Options.ScriptBatchTerminator = $false
96
97        $scripter.Options.Indexes = $true
98        $scripter.Options.ClusteredIndexes = $true
99        $scripter.Options.NonClusteredIndexes = $true
100        $scripter.Options.NoCollation = $true
101
102        $scripter.Options.DriAll = $true
103        $scripter.Options.DriIncludeSystemNames = $false
104
105        $scripter.Options.ToFileOnly = $true
106        $scripter.Options.Permissions = $true
107
108        foreach ($o in $objects | where {!($_.IsSystemObject)}) 
109        {
110            $typeFolder=$o.GetType().Name 
111
112            if (!(Test-Path -Path "$savepath$typeFolder")) 
113                { New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }
114
115            $file = $o -replace "[|]"
116            $file = $file.Replace("dbo.", "")
117
118            $scripter.Options.FileName = "$savePath$typeFolder$file.sql"
119            $scripter.Script($o)
120        }
121    }
122
123    catch
124    {
125        Util-Log-Error "`t`t$($MyInvocation.InvocationName): $_"
126    }
127}
128	
129using System.Data.SqlClient;
130using System.Collections.Specialized;
131using Microsoft.SqlServer.Management.Smo;
132	
133// For Me Server is ".SQLExpress" You may have changed
134        Server myServer = new Server(@".SQLExpress");
135        Scripter scripter = new Scripter(myServer);
136
137        //Databas1 is your database Name Thats Changable
138
139        Database myAdventureWorks = myServer.Databases["Database1"];
140        /* With ScriptingOptions you can specify different scripting  
141        * options, for example to include IF NOT EXISTS, DROP  
142        * statements, output location etc*/
143        ScriptingOptions scriptOptions = new ScriptingOptions();
144        scriptOptions.ScriptDrops = true;
145        scriptOptions.IncludeIfNotExists = true;
146        string scrs = "";
147        string tbScr = "";
148        foreach (Table myTable in myAdventureWorks.Tables)
149        {
150            /* Generating IF EXISTS and DROP command for tables */
151            StringCollection tableScripts = myTable.Script(scriptOptions);
152            foreach (string script in tableScripts)
153                scrs += script;
154
155            /* Generating CREATE TABLE command */
156            tableScripts = myTable.Script();
157            foreach (string script in tableScripts)
158                tbScr += script;
159        }
160        // For WinForms
161        MessageBox.Show(scrs + "nn" + tbScr);
162        //For Console
163        //Console.WriteLine(scrs + "nn" + tbScr);