private static int CountObjectDependancies(Scripter scripter, SqlSmoObject[] objectArray)
{
int retVal = 0;
DependencyWalker dependencyWalker = new DependencyWalker(scripter.Server);
DependencyTree dependencyTree = dependencyWalker.DiscoverDependencies(objectArray, true);
DependencyCollection dependencyCollection = dependencyWalker.WalkDependencies(dependencyTree);
foreach (DependencyCollectionNode oneDep in dependencyCollection)
{
if (oneDep.Urn.Type.Equals("View") ||
oneDep.Urn.Type.Equals("UserDefinedFunction") ||
oneDep.Urn.Type.Equals("StoredProcedure")
)
{
retVal++;
}
}
return(retVal);
}
private static List<View> SortViews(Database db, List<SqlSmoObject> sso)
{
var dw = new DependencyWalker(db.Parent);
var dt = new DependencyTree(dw.DiscoverDependencies(sso.ToArray(), true));
var views = new List<View>();
foreach (var d in dw.WalkDependencies(dt))
{
if (d.Urn.Type != "View")
continue;
var o = db.Views[d.Urn.GetNameForType(d.Urn.Type)];
if (o != null)
views.Add(db.Views[d.Urn.GetNameForType(d.Urn.Type)]);
}
return views;
}
private static void ProcessLevel(Level lvl, Database db, Server srv)
{
foreach (Level level in lvl.Levels)
{
Table table = db.Tables[level.Name];
if (table != null)
{
Index oldpk = table.Indexes.Cast <Index>().FirstOrDefault(index => index.IndexKeyType == IndexKeyType.DriPrimaryKey);
if (oldpk != null && oldpk.IndexedColumns.Count < 2)
{
DependencyWalker dw = new DependencyWalker(srv);
DependencyTree dependencies = dw.DiscoverDependencies(new SqlSmoObject[] { table }, DependencyType.Children);
DependencyTreeNode current = dependencies.FirstChild.FirstChild;
List <ForeignKey> oldfks = new List <ForeignKey>();
List <ForeignKey> newfks = new List <ForeignKey>();
_spsw.WriteLine();
_spsw.Write(table.Name + ":");
bool checkself = false;
while (current != null)
{
if (srv.GetSmoObject(current.Urn) is StoredProcedure sp)
{
_spsw.Write(sp.Name + "; ");
}
if (srv.GetSmoObject(current.Urn) is Table dependency)
{
foreach (ForeignKey oldfk in dependency.ForeignKeys)
{
if (oldfk.ReferencedTable == table.Name)
{
_dropfksw.WriteLine(string.Format(DROP_CONSTRAINT, dependency.Name, oldfk.Name));
ForeignKey newfk = new ForeignKey(dependency, oldfk.Name);
string columns1 = string.Empty;
string columns2 = string.Empty;
bool _first = true;
foreach (ForeignKeyColumn fkc in oldfk.Columns)
{
columns1 += (_first ? "" : ", ") + fkc.Name;
columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn;
_first = false;
newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn));
}
if (level.Parent != null)
{
foreach (ForeignKey fk in dependency.ForeignKeys)
{
if (fk.ReferencedTable == level.Parent.Name)
{
foreach (ForeignKeyColumn fkc in fk.Columns)
{
columns1 += (_first ? "" : ", ") + fkc.Name;
columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn;
_first = false;
newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn));
}
}
}
if (level.Parent.Parent != null)
{
foreach (ForeignKey fk in dependency.ForeignKeys)
{
if (fk.ReferencedTable == level.Parent.Parent.Name)
{
foreach (ForeignKeyColumn fkc in fk.Columns)
{
columns1 += (_first ? "" : ", ") + fkc.Name;
columns2 += (_first ? "" : ", ") + fkc.ReferencedColumn;
_first = false;
newfk.Columns.Add(new ForeignKeyColumn(newfk, fkc.Name, fkc.ReferencedColumn));
}
}
}
}
}
newfk.ReferencedTable = oldfk.ReferencedTable;
newfk.ReferencedTableSchema = oldfk.ReferencedTableSchema;
_addfksw.WriteLine(string.Format(ADD_FK_CONSTRAINT, dependency.Name, oldfk.Name, columns1, oldfk.ReferencedTable, columns2));
oldfks.Add(oldfk);
newfks.Add(newfk);
}
}
}
current = current.NextSibling;
if (current == null && !checkself)
{
current = dependencies.FirstChild;
checkself = true;
}
}
_droppksw.WriteLine(string.Format(DROP_CONSTRAINT, table.Name, oldpk.Name));
Index newpk = new Index(table, oldpk.Name);
string columns = string.Empty;
bool first = true;
foreach (IndexedColumn ic in oldpk.IndexedColumns)
{
columns += (first ? "" : ", ") + ic.Name;
first = false;
newpk.IndexedColumns.Add(new IndexedColumn(newpk, ic.Name));
}
if (level.Parent != null)
{
foreach (ForeignKey fk in table.ForeignKeys)
{
if (fk.ReferencedTable == level.Parent.Name)
{
foreach (ForeignKeyColumn fkc in fk.Columns)
{
columns += (first ? "" : ", ") + fkc.Name;
first = false;
newpk.IndexedColumns.Add(new IndexedColumn(newpk, fkc.Name));
}
}
}
if (level.Parent.Parent != null)
{
foreach (ForeignKey fk in table.ForeignKeys)
{
if (fk.ReferencedTable == level.Parent.Parent.Name)
{
foreach (ForeignKeyColumn fkc in fk.Columns)
{
columns += (first ? "" : ", ") + fkc.Name;
first = false;
newpk.IndexedColumns.Add(new IndexedColumn(newpk, fkc.Name));
}
}
}
}
}
newpk.IsClustered = true;
newpk.IsUnique = true;
newpk.IndexKeyType = IndexKeyType.DriPrimaryKey;
_addpksw.WriteLine(string.Format(ADD_PK_CONSTRAINT, table.Name, oldpk.Name, columns));
ProcessLevel(level, db, srv);
Console.Write(table.Name + "\r\n");
try
{
foreach (ForeignKey oldfk in oldfks)
{
oldfk.Drop();
table.Alter();
}
oldpk.Drop();
table.Alter();
newpk.Create();
foreach (ForeignKey newfk in newfks)
{
newfk.Create();
}
}
catch (Exception ex)
{
Console.Write(ex.Message + "\r\n");
}
}
}
}
}
/// <summary>
/// Runs the export.
/// </summary>
public void Run()
{
// Create server and database connection
this.server = new Server(this.config.server);
Database db = this.server.Databases[this.config.database];
// Collections for objects with and without dependancies
List <Urn> dependentObjects = new List <Urn>();
List <SqlSmoObject> independentObjectsFirst = new List <SqlSmoObject>();
List <DataTable> dtList = new List <DataTable>();
string[] names = Enum.GetNames(typeof(DatabaseObjectTypes));
DatabaseObjectTypes[] values = (DatabaseObjectTypes[])Enum.GetValues(typeof(DatabaseObjectTypes));
for (int i = 0; i < names.Length; i++)
{
string name = names[i];
if (name != "All" && Array.IndexOf(this.config.excludeTypes.ToUpper().Split(','), name.ToUpper()) == -1)
{
this.logger.Debug("Getting objects: " + name);
dtList.Add(db.EnumObjects(values[i]));
}
}
foreach (DataTable dt in dtList)
{
foreach (DataRow dr in dt.Rows)
{
// Select all objects, or only the named object
if (this.config.objectName == null || dr["Name"].Equals(this.config.objectName))
{
// Ignore excluded schemas
if (Array.IndexOf(Config.aExcludedSchemas, dr["Schema"]) == -1)
{
Urn urn = new Urn(dr["Urn"].ToString());
// Ignore excluded types
if (Array.IndexOf(this.config.excludeTypes.ToUpper().Split(','), urn.Type.ToUpper()) == -1)
{
// Split into dependant / independant types
if (Array.IndexOf(Config.aDependentTypeOrder, dr["DatabaseObjectTypes"].ToString()) != -1)
{
// Add the URN to the dependent object list
dependentObjects.Add(urn);
}
else
{
// Add the sqlSmoObject to the independent object list
SqlSmoObject sqlSmoObject = this.server.GetSmoObject(urn);
if (sqlSmoObject is ServiceBroker)
{
// For ServiceBroker object, add child BrokerServices and not self
foreach (BrokerService brokerService in (sqlSmoObject as ServiceBroker).Services)
{
this.independentObjectsLast.Add(brokerService);
}
}
else
{
// Other objects
independentObjectsFirst.Add(sqlSmoObject);
}
}
}
}
}
}
}
// Export database creation
if (this.config.scriptDatabase)
{
this.ScriptObject(db);
}
// Sort independent objects
SMOObjectComparer smoObjectComparer = new SMOObjectComparer();
independentObjectsFirst.Sort(smoObjectComparer);
this.independentObjectsLast.Sort(smoObjectComparer);
// Export starting independent objects
foreach (SqlSmoObject sqlSmoObject in independentObjectsFirst)
{
this.ScriptObject(sqlSmoObject);
}
// Get dependancy information, sort and export dependent objects
if (dependentObjects.Count > 0)
{
// Sort and export dependent objects types
DependencyWalker dependencyWalker = new DependencyWalker(this.server);
DependencyTree dependencyTree =
dependencyWalker.DiscoverDependencies(dependentObjects.ToArray(), DependencyType.Parents);
this.ScriptDependencyTreeNode(dependencyTree);
}
// Export ending independent objects
foreach (SqlSmoObject sqlSmoObject in this.independentObjectsLast)
{
this.ScriptObject(sqlSmoObject);
}
// Output file order information
if (this.fileBuildOrder.Count > 0)
{
// Get filename
string fileName = this.config.orderFilename;
this.logger.Debug("Creating Order file: " + fileName);
if (this.config.outputDirectory != null)
{
fileName = Path.Combine(this.config.outputDirectory, fileName);
}
// Create output directory if needed
string g = Path.GetDirectoryName(fileName);
if (g.Length != 0)
{
Directory.CreateDirectory(g);
}
StreamWriter sw = new StreamWriter(fileName);
foreach (string s in this.fileBuildOrder)
{
sw.WriteLine(s);
}
sw.Close();
}
}
public IEnumerable<DependencyCollectionNode> GetDependencies(SqlSmoObject smo)
{
var dependencyWalker = new DependencyWalker(_server);
var depTree = dependencyWalker.DiscoverDependencies(new[] { smo }, true);
return dependencyWalker.WalkDependencies(depTree);
}
public void EnumerateDatabase(string dbConnection, bool storeThreePartNames)
{
string serverName = string.Empty;
string loginName = string.Empty;
string password = string.Empty;
Server sqlServer;
ServerConnection sqlConnection;
Database sqlDatabase;
threePartNames = storeThreePartNames;
connectionStringBuilder.Clear();
connectionStringBuilder.ConnectionString = dbConnection;
try
{
sqlConnection = new ServerConnection(connectionStringBuilder.DataSource);
sqlConnection.ApplicationName = "DependencyAnalyzer";
if (connectionStringBuilder.IntegratedSecurity)
{
sqlConnection.LoginSecure = true;
}
else
{
sqlConnection.LoginSecure = false;
sqlConnection.Login = connectionStringBuilder.UserID;
sqlConnection.Password = connectionStringBuilder.Password;
}
sqlServer = new Server(sqlConnection);
sqlServer.SetDefaultInitFields(typeof(Table), "Name");
sqlServer.SetDefaultInitFields(typeof(Table), "Schema");
sqlServer.SetDefaultInitFields(typeof(Table), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(View), "Name");
sqlServer.SetDefaultInitFields(typeof(View), "Schema");
sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "Name");
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "Schema");
sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "Name");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "Schema");
sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");
int connectionID = repository.GetConnection(connectionStringBuilder.ConnectionString);
if (connectionID == -1)
{
// Need to add a new connectionID.
connectionID = repository.AddObject("CMD " + connectionStringBuilder.InitialCatalog, string.Empty, Repository.OLEDBGuid, repository.RootRepositoryObjectID);
repository.AddAttribute(connectionID, Repository.Attributes.ConnectionString, connectionStringBuilder.ConnectionString);
repository.AddAttribute(connectionID, Repository.Attributes.ConnectionServer, connectionStringBuilder.DataSource);
repository.AddAttribute(connectionID, Repository.Attributes.ConnectionDatabase, connectionStringBuilder.InitialCatalog);
}
sqlDatabase = sqlServer.Databases[connectionStringBuilder.InitialCatalog];
if (sqlDatabase == null)
{
throw new Exception(String.Format("{0} database was not found on server {1}", connectionStringBuilder.InitialCatalog, sqlServer.Name));
}
String databaseName = connectionStringBuilder.InitialCatalog;
DependencyWalker findDepenency = new DependencyWalker(sqlServer);
List <SqlSmoObject> walkerList = new List <SqlSmoObject>();
SqlSmoObject[] walkerArray;
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.AllowSystemObjects = false;
string objectName, parentName, objectServerName, objectDatabaseName;
int objectConnectionID = 0;
int tableID, parentID, arrayCounter;
DependencyTree dependTree;
DependencyTreeNode walkNode, parentNode;
Console.WriteLine("Enumerate the Tables and their Dependencies");
// Enumerate the Tables
arrayCounter = 0;
foreach (Table sqlTable in sqlDatabase.Tables)
{
if (!sqlTable.IsSystemObject)
{
walkerList.Add(sqlTable);
}
}
walkerArray = new SqlSmoObject[walkerList.Count];
foreach (SqlSmoObject smoObject in walkerList)
{
walkerArray[arrayCounter++] = smoObject;
}
if (walkerArray.Length > 0)
{
dependTree = findDepenency.DiscoverDependencies(walkerArray, DependencyType.Parents);
// Walk the tree
parentNode = dependTree.FirstChild;
do
{
objectName = FormatObjectName(parentNode.Urn); // "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
tableID = repository.GetTable(connectionID, objectName, parentNode.Urn.Type);
if (parentNode.HasChildNodes)
{
// Point at the first dependency
walkNode = parentNode.FirstChild;
do
{
parentName = FormatObjectName(walkNode.Urn); // "[" + (String.IsNullOrEmpty(walkNode.Urn.GetAttribute("Schema")) ? "dbo" : walkNode.Urn.GetAttribute("Schema")) + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
switch (walkNode.Urn.Type)
{
case "Table":
case "View":
parentID = repository.GetTable(connectionID, parentName, walkNode.Urn.Type);
break;
case "UserDefinedFunction":
parentID = repository.GetFunction(connectionID, parentName);
break;
case "StoredProcedure":
parentID = repository.GetProcedure(connectionID, parentName);
break;
case "PartitionScheme":
case "XmlSchemaCollection":
parentID = -1;
break;
default:
if (sqlDatabase.StoredProcedures.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetProcedure(connectionID, parentName);
}
else if (sqlDatabase.UserDefinedFunctions.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetFunction(connectionID, parentName);
}
else
{
Console.WriteLine("Warning: Unresolvable Dependency encountered in object {0} for object {1} of type {2}", objectName, parentName, walkNode.Urn.Type);
parentID = repository.GetTable(connectionID, parentName, "Unresolvable");
}
break;
}
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
walkNode = walkNode.NextSibling;
}while (walkNode != null);
}
parentNode = parentNode.NextSibling;
}while (parentNode != null);
}
Console.WriteLine("Enumerate the Views and their Dependencies");
walkerList.Clear();
arrayCounter = 0;
foreach (View sqlView in sqlDatabase.Views)
{
if (!sqlView.IsSystemObject)
{
walkerList.Add(sqlView);
}
}
walkerArray = new SqlSmoObject[walkerList.Count];
foreach (SqlSmoObject smoObject in walkerList)
{
walkerArray[arrayCounter++] = smoObject;
}
if (walkerArray.Length > 0)
{
dependTree = findDepenency.DiscoverDependencies(walkerArray, DependencyType.Parents);
// Walk the tree
parentNode = dependTree.FirstChild;
do
{
objectName = FormatObjectName(parentNode.Urn); // "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
// The following is because we don't know the type of a view in the TSQLParser...
tableID = repository.GetTable(connectionID, objectName, parentNode.Urn.Type);
if (parentNode.HasChildNodes)
{
// Point at the first dependency
walkNode = parentNode.FirstChild;
do
{
parentName = FormatObjectName(walkNode.Urn); // "[" + walkNode.Urn.GetAttribute("Schema") + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
objectServerName = walkNode.Urn.Parent.Parent.GetAttribute("Name");
objectDatabaseName = walkNode.Urn.Parent.GetAttribute("Name");
objectConnectionID = DetermineConnection(objectServerName, objectDatabaseName);
switch (walkNode.Urn.Type)
{
case "Synonym": // Synonym's look like tables in base T-SQL
case "Table":
case "View": // View's look like tables in base T-SQL
parentID = repository.GetTable(objectConnectionID, parentName, walkNode.Urn.Type);
break;
case "UserDefinedFunction":
parentID = repository.GetFunction(objectConnectionID, parentName);
break;
case "StoredProcedure":
parentID = repository.GetProcedure(objectConnectionID, parentName);
break;
case "PartitionScheme":
case "XmlSchemaCollection":
parentID = -1;
break;
default:
if (sqlDatabase.StoredProcedures.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetProcedure(objectConnectionID, parentName);
}
else if (sqlDatabase.UserDefinedFunctions.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetFunction(objectConnectionID, parentName);
}
else
{
Console.WriteLine("Warning: Unresolvable Dependency encountered in object {0} for object {1} of type {2}", objectName, walkNode.Urn, walkNode.Urn.Type);
Console.WriteLine("Attempting to locate via repository as table {0} on server {1} in database {2}", parentName, objectServerName, objectDatabaseName);
parentID = repository.GetTable(objectConnectionID, parentName, "Unresolved");
}
break;
}
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
walkNode = walkNode.NextSibling;
}while (walkNode != null);
}
parentNode = parentNode.NextSibling;
}while (parentNode != null);
}
Console.WriteLine("Enumerate the StoredProcedures and their Dependencies");
walkerList.Clear();
arrayCounter = 0;
foreach (StoredProcedure sqlProcedure in sqlDatabase.StoredProcedures)
{
if (!sqlProcedure.IsSystemObject)
{
walkerList.Add(sqlProcedure);
}
}
walkerArray = new SqlSmoObject[walkerList.Count];
foreach (SqlSmoObject smoObject in walkerList)
{
walkerArray[arrayCounter++] = smoObject;
}
if (walkerArray.Length > 0)
{
dependTree = findDepenency.DiscoverDependencies(walkerArray, DependencyType.Parents);
// Walk the tree
parentNode = dependTree.FirstChild;
do
{
objectName = FormatObjectName(parentNode.Urn); // "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
tableID = repository.GetProcedure(connectionID, objectName);
if (parentNode.HasChildNodes)
{
// Point at the first dependency
walkNode = parentNode.FirstChild;
do
{
parentName = FormatObjectName(walkNode.Urn); // "[" + walkNode.Urn.GetAttribute("Schema") + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
objectServerName = walkNode.Urn.Parent.Parent.GetAttribute("Name");
objectDatabaseName = walkNode.Urn.Parent.GetAttribute("Name");
objectConnectionID = DetermineConnection(objectServerName, objectDatabaseName);
switch (walkNode.Urn.Type)
{
case "Synonym": // Synonym's look like tables in base T-SQL
case "Table":
case "View": // View's look like tables in base T-SQL
parentID = repository.GetTable(objectConnectionID, parentName, walkNode.Urn.Type);
break;
case "UserDefinedFunction":
parentID = repository.GetFunction(objectConnectionID, parentName);
break;
case "StoredProcedure":
parentID = repository.GetProcedure(objectConnectionID, parentName);
break;
case "PartitionScheme":
case "XmlSchemaCollection":
parentID = -1;
break;
default:
if (sqlDatabase.StoredProcedures.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetProcedure(objectConnectionID, parentName);
}
else if (sqlDatabase.UserDefinedFunctions.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetFunction(objectConnectionID, parentName);
}
else
{
Console.WriteLine("Warning: Unresolvable Dependency encountered in object {0} for object {1} of type {2}", objectName, walkNode.Urn, walkNode.Urn.Type);
Console.WriteLine("Attempting to locate via repository as table {0} on server {1} in database {2}", parentName, objectServerName, objectDatabaseName);
parentID = repository.GetTable(objectConnectionID, parentName, "Unresolvable");
}
break;
}
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
walkNode = walkNode.NextSibling;
}while (walkNode != null);
}
parentNode = parentNode.NextSibling;
}while (parentNode != null);
}
Console.WriteLine("Enumerate the UserDefinedFunction and their Dependencies");
walkerList.Clear();
arrayCounter = 0;
foreach (UserDefinedFunction sqlFunctions in sqlDatabase.UserDefinedFunctions)
{
if (!sqlFunctions.IsSystemObject)
{
walkerList.Add(sqlFunctions);
}
}
walkerArray = new SqlSmoObject[walkerList.Count];
foreach (SqlSmoObject smoObject in walkerList)
{
walkerArray[arrayCounter++] = smoObject;
}
if (walkerArray.Length > 0)
{
dependTree = findDepenency.DiscoverDependencies(walkerArray, DependencyType.Parents);
// Walk the tree
parentNode = dependTree.FirstChild;
do
{
objectName = FormatObjectName(parentNode.Urn); // "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
// The following is because we don't know the type of a view in the TSQLParser...
tableID = repository.GetFunction(connectionID, objectName);
if (parentNode.HasChildNodes)
{
// Point at the first dependency
walkNode = parentNode.FirstChild;
do
{
parentName = FormatObjectName(walkNode.Urn); // "[" + walkNode.Urn.GetAttribute("Schema") + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
objectServerName = walkNode.Urn.Parent.Parent.GetAttribute("Name");
objectDatabaseName = walkNode.Urn.Parent.GetAttribute("Name");
objectConnectionID = DetermineConnection(objectServerName, objectDatabaseName);
switch (walkNode.Urn.Type)
{
case "Synonym": // Synonym's look like tables in base T-SQL
case "Table":
case "View": // View's look like tables in base T-SQL
parentID = repository.GetTable(objectConnectionID, parentName, walkNode.Urn.Type);
break;
case "UserDefinedFunction":
parentID = repository.GetFunction(objectConnectionID, parentName);
break;
case "StoredProcedure":
parentID = repository.GetProcedure(objectConnectionID, parentName);
break;
case "PartitionScheme":
case "XmlSchemaCollection":
parentID = -1;
break;
default:
if (sqlDatabase.StoredProcedures.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetProcedure(objectConnectionID, parentName);
}
else if (sqlDatabase.UserDefinedFunctions.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
{
parentID = repository.GetFunction(objectConnectionID, parentName);
}
else
{
Console.WriteLine("Warning: Unresolvable Dependency encountered in object {0} for object {1} of type {2}", objectName, walkNode.Urn, walkNode.Urn.Type);
Console.WriteLine("Attempting to locate via repository as table {0} on server {1} in database {2}", parentName, objectServerName, objectDatabaseName);
parentID = repository.GetTable(objectConnectionID, parentName, "Unresolvable");
}
break;
}
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
walkNode = walkNode.NextSibling;
}while (walkNode != null);
}
parentNode = parentNode.NextSibling;
}while (parentNode != null);
}
walkerList.Clear();
arrayCounter = 0;
foreach (Synonym sqlSynonym in sqlDatabase.Synonyms)
{
objectName = FormatObjectName(sqlSynonym.Urn); // "[" + sqlSynonym.Urn.GetAttribute("Schema") + "].[" + sqlSynonym.Urn.GetAttribute("Name") + "]";
// The following is because we don't know the type of a synonym in the TSQLParser...
tableID = repository.GetTable(connectionID, objectName, sqlSynonym.Urn.Type);
objectServerName = (sqlSynonym.BaseServer == string.Empty) ? sqlServer.Name : sqlSynonym.BaseServer;
objectDatabaseName = sqlSynonym.BaseDatabase;
objectConnectionID = DetermineConnection(objectServerName, objectDatabaseName);
if (threePartNames)
{
parentName = String.Format("[{0}].[{1}].[{2}]", objectDatabaseName, ((sqlSynonym.BaseSchema == string.Empty) ? "dbo" : sqlSynonym.BaseSchema), sqlSynonym.BaseObject);
}
else
{
parentName = String.Format("[{0}].[{1}]", ((sqlSynonym.BaseSchema == string.Empty) ? "dbo" : sqlSynonym.BaseSchema), sqlSynonym.BaseObject);
}
#if SQL2005
// SQL 2005 doesn't support the BaseType, so assume it's a table on the other end.
parentID = repository.GetTable(objectConnectionID, parentName);
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
#else
switch (sqlSynonym.BaseType.ToString())
{
case "Synonym": // Synonym's look like tables in base T-SQL
case "Table":
case "View": // View's look like tables in base T-SQL
parentID = repository.GetTable(objectConnectionID, parentName, sqlSynonym.BaseType.ToString());
break;
case "UserDefinedFunction":
parentID = repository.GetFunction(objectConnectionID, parentName);
break;
case "StoredProcedure":
parentID = repository.GetProcedure(objectConnectionID, parentName);
break;
case "PartitionScheme":
case "XmlSchemaCollection":
parentID = -1;
break;
default:
// Asume that this is pointing at a table...
parentID = repository.GetTable(objectConnectionID, parentName, sqlSynonym.BaseType.ToString());
break;
}
if (parentID > -1)
{
repository.AddMapping(parentID, tableID);
}
#endif
}
}
catch (Exception err)
{
Console.WriteLine(string.Format("Could not enumerate the database: {0}", err.Message));
}
}
