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);