dbscript/ScriptDBObjects.cs at master · msharp/dbscript · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
/*
 * *******************************************************************
 * Program: ScriptDbObjects.cs
 * Author: Max Sharples
 * Date: 2010-8-12
 * *******************************************************************

 This is a tool for creating DDL scripts from a SQLServer database
 The DDL scripts are generated within a specific file structure that
 can be used by dbscript.CreateDatabase to recreate the database.

 Example of file structure created by ScriptDbObjects

 /Databases (directory in local repository branch)
 /DatabaseName
 /SchemaObjects
 dbname.database.sql
 /Tables
 schema.tablename.table.sql *
 /Constraints
 dbo.tablename.contraintname.chkconst.sql *
 dbo.tablename.contraintname.defconst.sql *
 /Indexes
 dbo.tablename.indexname.index.sql *
 /Keys
 dbo.tablename.keyname.pkey.sql *
 /Triggers
 dbo.tablename.triggername.trigger.sql *
 /Views
 dbo.viewname.view.sql *
 /Indexes
 dbo.viewname.indexname.index.sql *
 /Triggers
 dbo.viewname.triggername.trigger.sql *
 /Stored Procedures
 dbo.sp_procedurename.proc.sql *
 /Functions
 dbo.fn_functionname.function *

 */

usingSystem;
usingSystem.IO;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data.SqlClient;
usingMicrosoft.SqlServer.Management.Smo;
usingMicrosoft.SqlServer.Management.Common;

namespacedbscript
{
classScriptDbObjects
{
publicScriptDbObjects(Connectionconnection,stringfilePath,stringdbName)
{

// ensure the provided path is available
if(!Directory.Exists(filePath))
{
Console.WriteLine("\nERROR: the Path \""+filePath+"\" does not exist\n");
return;
}

// start
DateTimebegan=DateTime.Now;
intobjectCount=0;

List<string>databases=newList<string>();
if(dbName.Length>0)databases.Add(dbName);

// if no database specified, script 'em all
if(databases.Count==0)databases=getAllDatabases(connection);

foreach(stringdbindatabases)
{
ScriptDBscr=newScriptDB(db,connection);
scr.scriptDB(filePath);

objectCount+=scr.objectCount;
}

// done
DateTimeended=DateTime.Now;
Console.WriteLine("\nProgram began "+began.ToLongTimeString()+", ended: "+ended.ToLongTimeString());
Console.WriteLine(objectCount.ToString()+" objects scripted.\n");
}

staticList<string>getAllDatabases(Connectionconnstr)
{
List<string>dbs=newList<string>();
stringcommand="SELECT "
+" name "
+"FROM "
+" master.dbo.sysdatabases "
+"WHERE "
+" name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) "
+"ORDER BY "
+" name";

SqlConnectioncn=newSqlConnection(connstr.connectionString());
cn.Open();
SqlCommandcmd=newSqlCommand(command,cn);

// issue the query
SqlDataReaderrdr=null;
try
{
rdr=cmd.ExecuteReader();
if(rdr!=null&&!rdr.IsClosed)
{
if(rdr.HasRows)
{
while(rdr.Read())
{
dbs.Add(rdr["name"].ToString());
}
}
}
}
catch(Exceptione)
{
Console.WriteLine("\nUnable to retrieve list of databases from server \n");
Console.WriteLine(e);
}

returndbs;
}

}
}