i have generated scripts manually through Generate script in tasks menu by right clicking database.
Now my problem is to generate that script through c# code...
My question is
is it possible to generate through c# code?
give me some tips in order to complete?
Waiting for your valuable suggestions and commands.
As it's already mentioned, you cas use SMO to do this, here is a an example using C# to script a database, I mentioned few options, but as it is in the post of @David Brabant, you can specify the values of many options.
public string ScriptDatabase()
{
var sb = new StringBuilder();
var server = new Server(@"ServerName");
var databse = server.Databases["DatabaseName"];
var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = true;
scripter.Options.IncludeHeaders = true;
//And so on ....
var smoObjects = new Urn[1];
foreach (Table t in databse.Tables)
{
smoObjects[0] = t.Urn;
if (t.IsSystemObject == false)
{
StringCollection sc = scripter.Script(smoObjects);
foreach (var st in sc)
{
sb.Append(st);
}
}
}
return sb.ToString();
}
This link may help you getting and scripting stored procedures
You can use sql smo for basically implementing all functionality available in SQL Server Enterprise manager. There is a nice tutorial here.
Edit: an example using SMO in PowerShell
function SQL-Script-Database
{
<#
.SYNOPSIS
Script all database objects for the given database.
.DESCRIPTION
This function scripts all database objects (i.e.: tables, views, stored
procedures, and user defined functions) for the specified database on the
the given server\instance. It creates a subdirectory per object type under
the path specified.
.PARAMETER savePath
The root path where to save object definitions.
.PARAMETER database
The database to script (default = $global:DatabaseName)
.PARAMETER DatabaseServer
The database server to be used (default: $global:DatabaseServer).
.PARAMETER InstanceName
The instance name to be used (default: $global:InstanceName).
.EXAMPLE
SQL-Script-Database c:\temp AOIDB
#>
param (
[parameter(Mandatory = $true)][string] $savePath,
[parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
[parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer,
[parameter(Mandatory = $false)][string] $InstanceName = $global:InstanceName
)
try
{
if (!$DatabaseServer -or !$InstanceName)
{ throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }
$ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
$db = $s.databases[$database]
$objects = $db.Tables
$objects += $db.Views
$objects += $db.StoredProcedures
$objects += $db.UserDefinedFunctions
$scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scripter.Options.AnsiFile = $true
$scripter.Options.IncludeHeaders = $false
$scripter.Options.ScriptOwner = $false
$scripter.Options.AppendToFile = $false
$scripter.Options.AllowSystemobjects = $false
$scripter.Options.ScriptDrops = $false
$scripter.Options.WithDependencies = $false
$scripter.Options.SchemaQualify = $false
$scripter.Options.SchemaQualifyForeignKeysReferences = $false
$scripter.Options.ScriptBatchTerminator = $false
$scripter.Options.Indexes = $true
$scripter.Options.ClusteredIndexes = $true
$scripter.Options.NonClusteredIndexes = $true
$scripter.Options.NoCollation = $true
$scripter.Options.DriAll = $true
$scripter.Options.DriIncludeSystemNames = $false
$scripter.Options.ToFileOnly = $true
$scripter.Options.Permissions = $true
foreach ($o in $objects | where {!($_.IsSystemObject)})
{
$typeFolder=$o.GetType().Name
if (!(Test-Path -Path "$savepath\$typeFolder"))
{ New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }
$file = $o -replace "\[|\]"
$file = $file.Replace("dbo.", "")
$scripter.Options.FileName = "$savePath\$typeFolder\$file.sql"
$scripter.Script($o)
}
}
catch
{
Util-Log-Error "`t`t$($MyInvocation.InvocationName): $_"
}
}
Based on @Sami Answer
I have create this Simple Function that will generate all the scripts for your Database( Tables, Views , stored procedures,Users and UserDefinedFunctions)
First: Get Required Assemblies
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies is the correct folder location (or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies on 64-bit systems).
You need to add references to:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.SqlEnum.dll
Second: use this Function
public static string ScriptDatabase()
{
// For Me Server is ".\SQLExpress" You may have changed
Server myServer = new Server(@".\SQLExpress");
Scripter scripter = new Scripter(myServer);
//Databas1 is your database Name Thats Changable
Database myAdventureWorks = myServer.Databases["MyDBName"];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
// scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = true;
scriptOptions.IncludeIfNotExists = true;
string scrs = "";
string tbScr = "";
foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
tbScr += script + "\n\n";
}
foreach (StoredProcedure mySP in myAdventureWorks.StoredProcedures)
{
/* Generating IF EXISTS and DROP command for StoredProcedures */
StringCollection tableScripts = mySP.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
/* Generating CREATE StoredProcedure command */
tableScripts = mySP.Script(scriptOptions);
foreach (string script in tableScripts)
tbScr += script + "\n\n";
}
foreach (View myView in myAdventureWorks.Views)
{
/* Generating IF EXISTS and DROP command for Views */
StringCollection tableScripts = myView.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
/* Generating CREATE Views command */
tableScripts = myView.Script(scriptOptions);
foreach (string script in tableScripts)
tbScr += script+"\n\n";
}
foreach (Microsoft.SqlServer.Management.Smo.User user in myAdventureWorks.Users)
{
/* Generating IF EXISTS and DROP command for Users */
StringCollection tableScripts = user.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script+"\n\n";
/* Generating CREATE Users command */
tableScripts = user.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
}
foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction userF in myAdventureWorks.UserDefinedFunctions)
{
/* Generating IF EXISTS and DROP command for UserDefinedFunctions */
StringCollection tableScripts = userF.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
/* Generating CREATE UserDefinedFunction command */
tableScripts = userF.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + "\n\n";
}
// For WinForms
return (scrs + "\n\n" + tbScr);
//For Console
//Console.WriteLine(scrs + "\n\n" + tbScr);
}
I've just been using the above answers to write a command line program for doing this in c# and thought I'd expand a bit on the answers above.
if you want to output data as well the schema you need to use:
scripter.EnumScript(something);
//instead of
scripter.Script(something);
The script method just checks for the IncludeData option and throws an exception if it is set, but you have to get on google to find out what the right method to use is! Interesting API design!
The relevant lists in the database are as follows:
database.Tables
database.Schemas
database.Views
database.StoredProcedures
database.UserDefinedFunctions
database.Users
database.Roles
database.Sequences
although that might not be exhaustive.
Getting rid of system objects
The lists in these objects are all custom types which are IEnumerable but not IEnumerable<T> so you can't do linq on them. This also means you have to to find out what type is in them and use foreach's implicit casts to get them out. I'd never used that in c# before but I guess this stuff is probably targeting framework 2.
A lot of them also have properties called IsSystemObject but this is not implementing an interface. At first it looks like it'll be a real pain to get rid of all the system objects but you can cull them all in one fell swoop by setting the following option:
options.AllowSystemObjects = false;
This works for everything except for Roles for those you have to do the system ones by hand:
foreach (DatabaseRole role in database.Roles)
{
if(role.IsFixedRole)
continue;
list.Add(role);
}
Adding objects for output
The process I used was to create an UrnCollection and then add the different list to the collection. Like this:
var list = new UrnCollection();
foreach (Schema schema in database.Schemas)
list.Add(schema.Urn);
//... more of these
scripter.EnumScript(list);
Options
From there you need to figure out what options to set to recreate the output you need. A few things to bear in mind:
See this post for more information on how to get foreign keys etc out.
Health warning
I started looking at this as a way to copy a database as I thought backup and restore wouldn't do what I wanted. After going quite a long way down the smo path and running into a lot of problems I had a bit of a re-evaluation and found backup and restore is a lot simpler for that use case.
Hopefully a it would guide you and upcoming ones.
You have to add following four references to your project to include following required namespaces
To add a references
Reference Microsoft.SqlServer.Smo.dll
namespaces
using System.Data.SqlClient;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Smo;
Now use following code in any function or button click event
// For Me Server is ".\SQLExpress" You may have changed
Server myServer = new Server(@".\SQLExpress");
Scripter scripter = new Scripter(myServer);
//Databas1 is your database Name Thats Changable
Database myAdventureWorks = myServer.Databases["Database1"];
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.IncludeIfNotExists = true;
string scrs = "";
string tbScr = "";
foreach (Table myTable in myAdventureWorks.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script;
/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
tbScr += script;
}
// For WinForms
MessageBox.Show(scrs + "\n\n" + tbScr);
//For Console
//Console.WriteLine(scrs + "\n\n" + tbScr);
It involved http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/ Answer (above) by David Brabant and the SO link above
Code Block 2 is used. Now you can use others as well
i could not find myserver there but it is resolved as well in above code.
Example for getting script for stored procedure. Note: I adding "GO" and "ALTER" by string operations. I spent 6 hours trying to figure out why it don't generate GO. The Microsoft Strikes Back: library SMO (Server Management Object) sucks big time, it's buggy and hard to work with!
var dbObj = database.StoredProcedures[name, schema];
if (dbObj != null)
{
Console.WriteLine(dbObj);
var script = new StringBuilder();
script.AppendLine("USE LifelongLearning");
script.AppendLine("GO");
script.AppendLine();
StringCollection scripts = dbObj.Script(scriptOptionsCreate);
foreach (string s in scripts)
{
if (s.Contains("CREATE ", StringComparison.OrdinalIgnoreCase))
{
string s2 = s.Replace("CREATE ", "ALTER ", StringComparison.OrdinalIgnoreCase);
script.AppendLine(s2);
}
else
{
script.AppendLine(s);
}
// it's a hack because scripting of the "GO" not working well
if (s == "SET ANSI_NULLS ON" || s == "SET QUOTED_IDENTIFIER ON")
script.AppendLine("GO");
}
script.AppendLine("GO");
string fileName = schema + "." + name + ".sql";
await File.WriteAllTextAsync(folder + "\\" + fileName, script.ToString());
}
readonly static ScriptingOptions scriptOptionsCreate = new ScriptingOptions()
{
//WithDependencies = true, // will add create table before create view
IncludeHeaders = false, /****** Object: View [dbo].[v_people] Script Date: 9/30/2021 6:03:10 PM ******/
ScriptSchema = true,
ScriptData = false,
Indexes = true,
ClusteredIndexes = true,
FullTextIndexes = true,
// GO attempt
FileName = "test.sql",
ScriptBatchTerminator = true,
NoCommandTerminator = false,
//ToFileOnly = true, // it makes script empty
AppendToFile = true,
EnforceScriptingOptions = true,
AllowSystemObjects = true,
Permissions = true,
DriAllConstraints = true,
SchemaQualify = true,
AnsiFile = true,
//AnsiPadding = false,
};
full working app is here: https://github.com/sam-klok/CreateSQLScriptsFromDB/blob/main/CreateSQLScriptsFromDB/Program.cs
