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);
public override void DocumentDatabases(EDocumentDatabasesType databasesType, ISelectionCriterias criterias)
{
StringBuilder RetVal = new StringBuilder();
RetVal.AppendLine(MakeSectionTitle($"Server : {_SqlServer.Name} - Databases list"));
switch (databasesType)
{
case EDocumentDatabasesType.Stats:
foreach (Database DatabaseItem in _SqlServer.Databases)
{
if ((criterias.SelectUserData && !DatabaseItem.IsSystemObject) || (criterias.SelectSystemData && DatabaseItem.IsSystemObject))
{
Log(string.Format("accessing database {0}", DatabaseItem.Name));
#region stats
RetVal.Append($"-- {DatabaseItem.Name.TrimEnd()}".AlignedLeft(40, '.'));
RetVal.Append($"{DatabaseItem.Size:#,##0.00 MB}".AlignedRight(20, '.'));
RetVal.Append($" {DatabaseItem.SpaceAvailable:#,##0.00 MB free}".AlignedRight(25, '.'));
RetVal.Append($" (D={DatabaseItem.DataSpaceUsage:#,##0 KB})".AlignedRight(15, '.'));
RetVal.Append($" (I={DatabaseItem.IndexSpaceUsage:#,##0 KB})".AlignedRight(20, '.'));
RetVal.Append($" {DatabaseItem.Users.Count} users".AlignedLeft(10, '.'));
DatabaseOptions oDBO;
try {
oDBO = DatabaseItem.DatabaseOptions;
RetVal.Append($" | {SqlUtils.RecoveryModelText(oDBO.RecoveryModel)}");
} catch {
RetVal.Append(" | only for 2000+");
}
RetVal.AppendLine();
}
}
break;
#endregion
case EDocumentDatabasesType.Physical:
#region physical
const string COL_DBNAME = "Db name";
const int COL_DBNAME_WIDTH = 30;
const string COL_LOGICAL_FILENAME = "Logical name";
const int COL_LOGICAL_FILENAME_WIDTH = 40;
const string COL_GROUP_NAME = "Group";
const int COL_GROUP_NAME_WIDTH = 20;
const string COL_PHYSICAL_FILENAME = "Physical name";
const int COL_PHYSICAL_FILENAME_WIDTH = 120;
const string COL_CURRENT_SIZE = "Current size";
const int COL_CURRENT_SIZE_WIDTH = 20;
const string COL_FREE_SPACE = "Free space";
const int COL_FREE_SPACE_WIDTH = 20;
const string COL_GROWTH = "Growth parameters";
#region --- Columns header --------------------------------------------
RetVal.Append(COL_DBNAME.AlignedCenter(COL_DBNAME_WIDTH));
RetVal.Append(COL_LOGICAL_FILENAME.AlignedCenter(COL_LOGICAL_FILENAME_WIDTH));
RetVal.Append(COL_GROUP_NAME.AlignedCenter(COL_GROUP_NAME_WIDTH));
RetVal.Append(COL_PHYSICAL_FILENAME.AlignedCenter(COL_PHYSICAL_FILENAME_WIDTH));
RetVal.Append(COL_CURRENT_SIZE.AlignedCenter(COL_CURRENT_SIZE_WIDTH));
RetVal.Append(COL_FREE_SPACE.AlignedCenter(COL_FREE_SPACE_WIDTH));
RetVal.Append(COL_GROWTH);
RetVal.AppendLine();
#endregion --- Columns header --------------------------------------------
foreach (Database DatabaseItem in _SqlServer.Databases)
{
if ((criterias.SelectUserData && !DatabaseItem.IsSystemObject) || (criterias.SelectSystemData && DatabaseItem.IsSystemObject))
{
Log(string.Format("accessing database {0}", DatabaseItem.Name));
#region Data files
foreach (FileGroup FileGroupItem in DatabaseItem.FileGroups)
{
foreach (DataFile DataFileItem in FileGroupItem.Files)
{
RetVal.Append($"{DatabaseItem.Name.AlignedLeft(COL_DBNAME_WIDTH, '.')}");
RetVal.Append($"{DataFileItem.Name.AlignedLeft(COL_LOGICAL_FILENAME_WIDTH, '.')}");
RetVal.Append($"{FileGroupItem.Name.AlignedLeft(COL_GROUP_NAME_WIDTH, '.')}");
RetVal.Append($"{DataFileItem.FileName.AlignedLeft(COL_PHYSICAL_FILENAME_WIDTH, '.')}");
RetVal.Append($"{(float)(DataFileItem.Size / 1024.0):#0.00 MB}".AlignedRight(COL_CURRENT_SIZE_WIDTH, '.'));
RetVal.Append($"{DataFileItem.VolumeFreeSpace:#0.00 MB}".AlignedRight(COL_FREE_SPACE_WIDTH, '.'));
switch (DataFileItem.GrowthType)
{
case FileGrowthType.KB:
#region Growth MB
if (DataFileItem.Growth > 0)
{
RetVal.Append($" + {(int)(DataFileItem.Growth / 1024):#0 MB}");
if (DataFileItem.MaxSize > 0)
{
RetVal.Append($", upto {DataFileItem.MaxSize:#0 MB}");
}
else
{
RetVal.Append(", no limit");
}
}
else
{
RetVal.Append(" + no growth");
}
break;
#endregion
case FileGrowthType.Percent:
#region Growth Percent
if (DataFileItem.Growth > 0)
{
RetVal.Append($" + {DataFileItem.Growth:#0} % ");
if (DataFileItem.MaxSize > 0)
{
RetVal.Append($", upto {DataFileItem.MaxSize:#0 MB}");
}
else
{
RetVal.Append(", no limit");
}
}
else
{
RetVal.Append(" + no growth");
}
break;
#endregion
default:
RetVal.Append(" + no growth");
break;
}
RetVal.AppendLine();
}
}
#endregion
#region Log files
foreach (LogFile LogFileItem in DatabaseItem.LogFiles)
{
RetVal.Append($"{DatabaseItem.Name.AlignedLeft(COL_DBNAME_WIDTH, '.')}");
RetVal.Append($"{LogFileItem.Name.AlignedLeft(COL_LOGICAL_FILENAME_WIDTH, '.')}");
RetVal.Append($"{string.Empty.PadRight(COL_GROUP_NAME_WIDTH, '.')}");
RetVal.Append($"{LogFileItem.FileName.AlignedLeft(COL_PHYSICAL_FILENAME_WIDTH, '.')}");
RetVal.Append($"{(float)(LogFileItem.Size / 1024.0):#0.00 MB}".AlignedRight(COL_CURRENT_SIZE_WIDTH, '.'));
RetVal.Append($"{LogFileItem.VolumeFreeSpace:#0.00 MB}".AlignedRight(COL_FREE_SPACE_WIDTH, '.'));
switch (LogFileItem.GrowthType)
{
case FileGrowthType.KB:
#region Growth MB
if (LogFileItem.Growth > 0)
{
RetVal.Append($" + {(float)(LogFileItem.Growth / 1024):#0 MB}");
if (LogFileItem.MaxSize > 0)
{
RetVal.Append($", upto {LogFileItem.MaxSize:#0 MB}");
}
else
{
RetVal.Append(", no limit");
}
}
else
{
RetVal.Append(" + no growth");
}
break;
#endregion
case FileGrowthType.Percent:
#region Growth Percent
if (LogFileItem.Growth > 0)
{
RetVal.Append($" + {LogFileItem.Growth:#0} % ");
if (LogFileItem.MaxSize > 0)
{
RetVal.Append($", upto {LogFileItem.MaxSize:#0 MB}");
}
else
{
RetVal.Append(", no limit");
}
}
else
{
RetVal.Append(" + no growth");
}
break;
#endregion
default:
RetVal.Append(" + no growth");
break;
}
RetVal.AppendLine();
}
#endregion
}
}
break;
#endregion
case EDocumentDatabasesType.Full:
case EDocumentDatabasesType.List:
default:
#region list
foreach (Database DatabaseItem in _SqlServer.Databases)
{
if ((criterias.SelectUserData && !DatabaseItem.IsSystemObject) || (criterias.SelectSystemData && DatabaseItem.IsSystemObject))
{
Log(string.Format("accessing database {0}", DatabaseItem.Name));
RetVal.Append($"-- Database : {DatabaseItem.Name.AlignedLeft(50, '.')}");
RetVal.Append($" {SqlUtils.DbStatusText(DatabaseItem.Status)}");
RetVal.AppendLine();
}
}
break;
#endregion
}
Output?.Invoke(RetVal.ToString());
}
public abstract void DocumentDatabases(EDocumentDatabasesType databasesType, ISelectionCriterias criterias);
