public override void DocumentTables(Database database, EDocumentTablesType tablesType, ISelectionCriterias criterias)
{
StringBuilder RetVal = new StringBuilder();
RetVal.AppendLine(MakeSectionTitle($"Database : {database.Name} - Tables list"));
foreach (Table TableItem in database.Tables)
{
if (criterias.Filter.IsEmpty() || criterias.Filter.Contains(TableItem.Name, StringComparer.InvariantCultureIgnoreCase))
{
if ((criterias.SelectUserData && !database.IsSystemObject && !TableItem.IsSystemObject) || (criterias.SelectSystemData && database.IsSystemObject && TableItem.IsSystemObject))
{
Scripter TableScripter = new Scripter(_SqlServer);
TableScripter.Options.ScriptDrops = false;
TableScripter.Options.WithDependencies = true;
TableScripter.Options.IncludeHeaders = true;
string strTableName;
string strUnderline;
switch (tablesType)
{
case EDocumentTablesType.Full:
#region full
strTableName = $"-- > Table : {database.Name}.{database.UserName}.{TableItem.Name}";
strUnderline = "-- > " + new string('=', strTableName.Length - 5);
RetVal.AppendLine(strUnderline);
RetVal.AppendLine(strTableName);
RetVal.AppendLine(strUnderline);
foreach (string LineItem in TableScripter.Script(new SqlSmoObject[] { TableItem }))
{
RetVal.AppendLine(LineItem);
}
RetVal.AppendLine("-- > ===[EOT]===");
RetVal.AppendLine();
break;
#endregion
/**
* //case "stats":
* // #region stats
* // strTableName = string.Format("-- > Table : {0}.{1}.{2}", oDB.Name, oDB.UserName, TableItem.Name);
* // Console.Write(strTableName.PadRight(80, '.'));
* // Console.Write(" (D={0,20}, ", TableItem.DataSpaceUsed.ToString("#,##0 KB"));
* // Console.Write("{0,20}) ", TableItem.Rows.ToString("#,##0 recs"));
* // Console.Write("(I={0,20})", TableItem.IndexSpaceUsed.ToString("#,##0 KB"));
* // Console.WriteLine();
* // break;
* //#endregion
*
* //case "indexes":
* // #region indexes
* // try {
* // oTable.RecalcSpaceUsage();
* // } catch (Exception ex) {
* // Trace.WriteLine(string.Format("Error calculating space usage for {0}", oTable.Name));
* // Trace.WriteLine(ex.Message, "Error");
* // }
* // strTableName = string.Format("-- > Table : {0}.{1}.{2}", oDB.Name, oDB.UserName, TableItem.Name);
* // Console.Write(strTableName.PadRight(80, '.'));
* // Console.Write(" (I={0,20})", TableItem.IndexSpaceUsed.ToString("#,##0 KB"));
* // Console.Write(" (D={0,20}, ", TableItem.DataSpaceUsed.ToString("#,##0 KB"));
* // Console.Write("{0,20}) ", TableItem.Rows.ToString("#,##0 recs"));
* // Console.WriteLine();
* // foreach (Index2 oIndex in TableItem.Indexes) {
* // if (!UserOnly || (UserOnly && !oIndex.Name.StartsWith("_WA_Sys_"))) {
* // Console.Write(" Index : {0}", oIndex.Name.PadRight(67, '.'));
* // Console.Write(" Size = {0,16}, ", oIndex.SpaceUsed.ToString("#,##0 KB"));
* // Console.Write("Key = ");
* // SQLDMO.SQLObjectList oSqlObjectList = oIndex.ListIndexedColumns();
* // StringBuilder sbText = new StringBuilder();
* // foreach (SQLDMO.Column oColumn in oSqlObjectList) {
* // sbText.AppendFormat("{0}+", oColumn.Name);
* // }
* // sbText.Remove(sbText.Length - 1, 1);
* // Console.Write(sbText.ToString());
*
* // Console.Write(", Type = {0}", SqlUtils.IndexType2String(oIndex.Type));
* // if (oIndex.FillFactor > 0) {
* // Console.Write(", Fill factor = {0}%", oIndex.FillFactor);
* // }
* // Console.Write(", Storage = {0}", oIndex.FileGroup);
* // Console.WriteLine();
* // }
* // }
* // Console.WriteLine();
* // break;
* //#endregion
*
* //case "comments":
* // strTableName = string.Format("-- > Table : {0}.{1}.{2}", oDB.Name, oDB.UserName, TableItem.Name);
* // strUnderline = "-- > " + new string('=', strTableName.Length - 5);
* // Console.WriteLine(strUnderline);
* // Console.WriteLine(strTableName);
* // Console.WriteLine(strUnderline);
* // string SqlCommand = string.Format("SELECT objname, convert(varchar(512), value) as comment FROM ::fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', '{0}', 'column', NULL)", TableItem.Name);
* // DataTable ExtendedProperties = SqlUtils.QR2DataTable(oDB.ExecuteWithResults(SqlCommand, null));
* // foreach (SQLDMO.Column oColumnItem in TableItem.Columns) {
* // Console.Write("{0}", oColumnItem.Name.PadRight(30, '.'));
* // Console.Write(" {0}", oColumnItem.Length.ToString().PadLeft(8, '.'));
* // Console.Write(" {0}", oColumnItem.Datatype.PadRight(10, '.'));
* // Console.Write(" {0}", oColumnItem.AllowNulls ? "NULL....." : "NOT NULL.");
* // if (oColumnItem.Default != null) {
* // Console.Write(" {0}", oColumnItem.Default.PadRight(20, '.'));
* // }
* // string ExtendedPropertyComment = "";
* // DataRow[] oRows = { };
* // if (ExtendedProperties.Rows.Count > 0) {
* // oRows = ExtendedProperties.Select(string.Format("objname = '{0}'", oColumnItem.Name));
* // if (oRows.Length > 0) {
* // ExtendedPropertyComment = Encoding.Unicode.GetString(Encoding.Unicode.GetBytes(oRows[0]["comment"].ToString()));
* // }
* // }
* // int CommentSizeLimit = 100;
* // if (ExtendedPropertyComment.Length <= CommentSizeLimit) {
* // Console.Write(" {0}", ExtendedPropertyComment);
* // Console.WriteLine();
* // } else {
* // string Filler = new string(' ', 62);
* // int WhitePos = 80;
* // while (WhitePos > 1 && ExtendedPropertyComment[WhitePos] != ' ') {
* // WhitePos--;
* // }
* // Console.Write(" {0}", ExtendedPropertyComment.Substring(0, WhitePos));
* // ExtendedPropertyComment = ExtendedPropertyComment.Substring(WhitePos);
* // Console.WriteLine();
* // while (ExtendedPropertyComment.Length > CommentSizeLimit) {
* // WhitePos = 80;
* // while (WhitePos > 1 && ExtendedPropertyComment[WhitePos] != ' ') {
* // WhitePos--;
* // }
* // Console.WriteLine(Filler + ExtendedPropertyComment.Substring(0, WhitePos));
* // ExtendedPropertyComment = ExtendedPropertyComment.Substring(WhitePos);
* // }
* // if (ExtendedPropertyComment.Length > 0) {
* // Console.WriteLine(Filler + ExtendedPropertyComment);
* // }
* // }
*
* // }
* // Console.WriteLine();
* // break;
**/
case EDocumentTablesType.List:
default:
#region list
RetVal.AppendLine($"-- > Table : {database.Name}.{database.UserName}.{TableItem.Name}");
break;
#endregion
}
/**
* //#region Triggers
* //if (WithTriggers) {
* // string strTriggerName;
* // string strUnderTrigger;
* // switch (TriggerDetails) {
* // case "full":
* // #region full
* // foreach (Trigger oTrigger in TableItem.Triggers) {
* // strTriggerName = string.Format("-- Trigger : {0}.{1}.{2}.{3}", oDB.Name, oDB.UserName, TableItem.Name, oTrigger.Name);
* // strUnderTrigger = "-- " + new string('=', strTriggerName.Length - 3);
* // Console.WriteLine(strUnderTrigger);
* // Console.WriteLine(strTriggerName);
* // Console.WriteLine(strUnderTrigger);
* // Console.WriteLine(oTrigger.Text);
* // Console.WriteLine("-- ===[EOT]===");
* // Console.WriteLine();
* // }
* // break;
* // #endregion
* // case "list":
* // case "":
* // default:
* // #region list
* // foreach (Trigger oTrigger in TableItem.Triggers) {
* // strTriggerName = string.Format("-- > > Trigger : {0}.{1}.{2}.{3}", oDB.Name, oDB.UserName, TableItem.Name, oTrigger.Name);
* // Console.WriteLine(strTriggerName);
* // }
* // break;
* // #endregion
* // }
* //}
* //#endregion
**/
}
}
}
Output?.Invoke(RetVal.ToString());
}
public abstract void DocumentTables(Database database, EDocumentTablesType tablesType, ISelectionCriterias criterias);
