Introduction
Database is an integral part of any company/project. They range from small scale to large scale. But most of the times, if you ask for a data dictionary, then 'boom' - nobody has a data dictionary. This tool will enable you to create data dictionary for your tables and export them to an Excel file. As an added feature, this tool will also let you know about the primary key and foreign key relations with other tables of the database.
Background
I worked in several places where I found this important feature missing. They have databases, but no data dictionary. From time to time, the need is realized and people just ponder on the databases to have a birds' eye view of the database and tables, get the business going with necessary amendments and forget about it. Along with this, I felt the need to know which other tables use the primary keys of a table as foreign key. That is, if 'EmployeeID' is the primary key of a table, I needed to know which other tables are using it as a foreign keys. Similarly, I felt the need to know the primary tables that use the foreign keys of a table as primary keys. That is, if 'ItemID' is a foreign key in a table, then I needed to know which table has this key as the primary key. Considering all these needs, I have put together all of them in this tool.
The Interface
The interface is simple. Select the database first, then select the table. After that, edit the description and finally export to Excel file. The interface is like the following:
This is an example of 'aspnetdb' database and 'aspnet_Membership' table. Only the Description, Example, and Notes. The ability to enable/disable editing is provided in the following portion after all the columns are loaded successfully.
foreach (DataGridViewColumn Col in ColumnsGridView.Columns)
Col.ReadOnly = !(Col.Name.Equals("Description") || Col.Name.Equals("Example") ||
Col.Name.Equals("Notes"));
The two other datagrids below display the primary keys and foreign keys relationships with other tables; these are read-only grids.
Below the table combo box, there are two radio-buttons to indicate the examples choice of the user. If the user chooses to load examples from the config file, then it does so. On the other hand, if the user chooses to populate examples from the database, then it loads examples from the database. Please mind that including live data in the Excel output might lead to confidential issues, especially if the data contains confidential information. A typical example is HIV patients' data which is highly confidential. Example data for such tables should be populated from the config file, or if it is to reflect real examples, then care must be taken to obscure any kind of identity.
At the end of the screen, there is an indication of the current record count for this table, the average disk space occupied by each record of the table, and the total disk space occupied by the entire table.
After successful modification, the dictionary can be exported to an Excel file with the button click. This also exports the key relations and a rough estimate of disk space occupied by a single record of the table.
Using the Code
Part 1: An Introduction to the Classes of the Project
PrimaryKeyClass.cs
This class is for keeping information about the primary keys of a table. This class has another class inside which actually keeps information about each primary key of a table. This class is defined as follows. The PrimaryKeyName is the name of the column which is a primary key of the table in concern. The ForeignKeyTable is the table name where this column is used as a foreign key. The NameInForeignKeyTable is the name of the corresponding column in the foreign table (column names might be different).
class PKKeyCriteria
{
public string PrimaryKeyName { get; set; }
public string ForeignKeyTable { get; set; }
public string NameInForeignKeyTable { get; set; }
}
The PrimaryKeyClass.cs itself has a list object (PKList) which gradually bulks up with all the primary keys.
public List<PKKeyCriteria> PKList = new List<PKKeyCriteria>();
The actual method that is used to bulk up the primary key list is the GetPrimaryKeysAndDependencies() method of the class.
public void GetPrimaryKeysAndDependencies()
{
SqlDataAdapter DAdapter = new SqlDataAdapter();
DataTable DTable = new DataTable();
using (var Cmd = new SqlCommand("sp_fkeys", Conn))
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.AddWithValue("@pktable_name", TableName);
DAdapter.SelectCommand = Cmd;
DAdapter.Fill(DTable);
}
...............................
...............................
In this method, we use SP_FKEYS which is a built-in Stored Procedure of SQL Server. We pass our table name as a parameter to the SP. The SP returns the following columns for the table.
We are interested in only the PKCOLUMN_NAME, FKTABLE_NAME and FKCOLUMN_NAME. So we list them in PKList.
foreach (DataRow DR in DTable.Rows)
{
PrimaryKeyClass KeyItem = new PrimaryKeyClass();
KeyItem.PrimaryKeyName = DR["PKCOLUMN_NAME"].ToString();
KeyItem.ForeignKeyTable = DR["FKTABLE_NAME"].ToString();
KeyItem.NameInForeignKeyTable = DR["FKCOLUMN_NAME"].ToString();
PKList.Add(KeyItem);
}
However, for very rare cases, the primary keys might not be obtained from the above method. In such case, we use the FillSchema() method of data adapter and check the PrimaryKey column. However in this case we miss the tables where this primary key is used as a foreign key.
if (PKList.Count == 0)
using (var cmd = new SqlCommand("SELECT * FROM [" + TablesCmb.Text + "]", Conn))
{
DAdapter = new SqlDataAdapter();
DAdapter.SelectCommand = cmd;
DAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
var dtab = new DataTable();
DAdapter.FillSchema(dtab, SchemaType.Source);
foreach (DataColumn col in dtab.PrimaryKey)
{
PrimaryKeyClass KeyItem = new PrimaryKeyClass();
KeyItem.PrimaryKeyName = col.ColumnName;
PKList.Add(KeyItem);
}
}
ForeignKeyClass.cs
This class is for keeping information about the foreign keys of a table. This class has another class inside which actually keeps information about each foreign key of a table. This class is defined as follows. The ForeignKeyName is the name of the column which is a foreign key of the table in concern. The PrimaryKeyTable is the table name where this column is a primary key. The NameInPrimaryKeyTable is the name of the corresponding column in the primary table (column names might be different).
class FKKeyCriteria
{
public string ForeignKeyName { get; set; }
public string PrimaryKeyTable { get; set; }
public string NameInPrimaryKeyTable { get; set; }
}
The ForeignKeyClass.cs itself has a list object (FKList) which gradually bulks up with all the foreign keys.
public List<FKKeyCriteria> FKList = new List<FKKeyCriteria>();
The actual method that is used to bulk up the foreign key list is the GetForeignKeysAndDependencies() method of the class. This method actually executes the following script which is adapted from here.
SqlCommand Cmd = new SqlCommand();
//Code adapted from http://stackoverflow.com/questions/483193/
//how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server
Cmd.CommandText = "SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), " +
"PKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O1.SCHEMA_ID)), " +
"PKTABLE_NAME = CONVERT(SYSNAME, O1.NAME), " +
"PKCOLUMN_NAME = CONVERT(SYSNAME, C1.NAME), " +
"FKTABLE_QUALIFIER = CONVERT(SYSNAME, DB_NAME()), " +
"FKTABLE_OWNER = CONVERT(SYSNAME, SCHEMA_NAME(O2.SCHEMA_ID)), " +
"FKTABLE_NAME = CONVERT(SYSNAME, O2.NAME), " +
"FKCOLUMN_NAME = CONVERT(SYSNAME, C2.NAME), " +
"UPDATE_RULE = CONVERT(SMALLINT, CASE OBJECTPROPERTY_
(F.OBJECT_ID, 'CnstIsUpdateCascade') " +
"WHEN 1 THEN 0 " +
"ELSE 1 " +
"END), " +
"DELETE_RULE = CONVERT(SMALLINT, CASE OBJECTPROPERTY_
(F.OBJECT_ID, 'CnstIsDeleteCascade') " +
"WHEN 1 THEN 0 " +
"ELSE 1 " +
"END), " +
"FK_NAME = CONVERT(SYSNAME, OBJECT_NAME(F.OBJECT_ID)), " +
"PK_NAME = CONVERT(SYSNAME, I.NAME), " +
"DEFERRABILITY = CONVERT(SMALLINT, 7)" +
"FROM SYS.ALL_OBJECTS O1, " +
"SYS.ALL_OBJECTS O2, " +
"SYS.ALL_COLUMNS C1, " +
"SYS.ALL_COLUMNS C2, " +
"SYS.FOREIGN_KEYS F " +
"INNER JOIN SYS.FOREIGN_KEY_COLUMNS K " +
"ON(K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID) " +
"INNER JOIN SYS.INDEXES I " +
"ON(F.REFERENCED_OBJECT_ID = I.OBJECT_ID " +
"AND F.KEY_INDEX_ID = I.INDEX_ID) " +
"WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID " +
"AND O2.OBJECT_ID = F.PARENT_OBJECT_ID " +
"AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID " +
"AND C2.OBJECT_ID = F.PARENT_OBJECT_ID " +
"AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID " +
"AND C2.COLUMN_ID = K.PARENT_COLUMN_ID " +
"AND CONVERT(SYSNAME, O2.NAME) = '" + TablesCmb.Text + "'";
Cmd.Connection = Conn;
SqlDataAdapter DAdapter = new SqlDataAdapter();
DataTable DTable = new DataTable();
DAdapter.SelectCommand = Cmd;
DAdapter.Fill(DTable);
Basically, it returns the same table structure as for the primary keys, but this time, the script focuses on the foreign keys.
DataDetailsClass.cs
This class is for keeping information about database column details. This class has another class inside which actually keeps information about each individual column of a table. This class is defined as follows. The purposes of the members are self-explanatory by their names.
class DataDetailsKeyCriteriaClass
{
public string ColumnName { get; set; }
public long ColumnSize { get; set; }
public string DataType { get; set; }
public string SqlDataType { get; set; }
public string Mandatory { get; set; }
public string PrimaryKey { get; set; }
public string UniqueKey { get; set; }
public string ForeignKey { get; set; }
public string Description { get; set; }
public string Example { get; set; }
public string RangeFrom { get; set; }
public string RangeTo { get; set; }
public string Notes { get; set; }
public string Computed { get; set; }
}
The last member 'Computed' is newly added to this version to mean if the column is a computed column or not.
The DataDetailsClass.cs itself has a list object (ColumnList) which gradually bulks up with each object of the above class.
public List<DataDetailsKeyCriteriaClass> ColumnList = new List<DataDetailsKeyCriteriaClass>();
Following is the actual method that is used to bulk up the column list. Operationally, our main form will call this method and pass the primary keys, foreign keys, unique keys of a table. It also passes an out variable for calculating a rough estimate of space occupied by each record; the method returns the total size in this variable. This method basically reads the schema information of a table; it uses CommandBehavior.SchemaOnly and GetSchemaTable() methods for this (see below).
public void GetColumnDetails(List<PKKeyCriteria> PKList,
List<FKKeyCriteria> FKList, List<string> UniqueIndexList,
out string DiskSpace, EXAMPLE_CHOICE ExampleChoiceParam)
{
DiskSpace = "";
List<string> ComputedColumns = new List<string>();
GetComputedColumns(ref ComputedColumns);
using (var cmd = new SqlCommand("SELECT * FROM [" + TableName + "]", Conn))
using (var DReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
DataTable table = DReader.GetSchemaTable();
int Index = 0;
foreach (DataRow row in table.Rows)
{
DataDetailsKeyCriteriaClass Details = new DataDetailsKeyCriteriaClass();
Details.ColumnName = row["ColumnName"].ToString();
if (ComputedColumns.FindIndex(p => p == Details.ColumnName) == -1)
{
Details.ColumnSize = Convert.ToInt64(row["ColumnSize"]);
Details.DataType = ((Type)row["DataType"]).Name;
Details.SqlDataType = row["DataTypeName"].ToString();
Details.Description = GetKeyValueFromAppConfig
(Details.SqlDataType + "_Description", Details.ColumnSize);
Details.Example = ExampleChoiceParam == EXAMPLE_CHOICE.HardCoded ?
GetKeyValueFromAppConfig(Details.SqlDataType + "_Example") :
Details.Example = GetLiveExample(Details.ColumnName);
Details.RangeFrom = GetKeyValueFromAppConfig
(Details.SqlDataType + "_RangeFrom");
Details.RangeTo = GetKeyValueFromAppConfig
(Details.SqlDataType + "_RangeTo");
Details.Mandatory =
row["AllowDBNull"].ToString().Equals("True") ? "No" : "Yes";
Index = PKList.FindIndex
(Item => Item.PrimaryKeyName.Equals(Details.ColumnName));
Details.PrimaryKey = (Index != -1) ? "Yes" : "No";
Index = UniqueIndexList.FindIndex(Item => Item.Equals(Details.ColumnName));
Details.UniqueKey = (Index != -1) ? "Yes" : "No";
Index = FKList.FindIndex
(Item => Item.ForeignKeyName.Equals(Details.ColumnName));
Details.ForeignKey = (Index != -1) ? "Yes" : "No";
Details.Computed = "No";
}
else
{
Details.Computed = "Yes";
if (ThisIsAPersistedComputedColumn(Details.ColumnName))
Details.ColumnSize = Convert.ToInt64(row["ColumnSize"]);
}
ColumnList.Add(Details);
}
}
DiskSpace = RetrieveDiskSpaceDetails();
}
In summary, we are trying to list all the columns in the columns list ('ColumnList'). We obtain the schema only using the CommandBehavior.SchemaOnly. At the same time, we obtain a list of computed columns which might be present in the current table (in list 'ComputedColumns'). This is done in the following method. The working portion is adapted from here.
private void GetComputedColumns(ref List<string> ComputedColumns)
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM sys.columns
WHERE is_computed = 1 AND object_id = OBJECT_ID('" + TableName + "')", Conn))
using (SqlDataReader DReader = cmd.ExecuteReader())
{
while (DReader.Read())
ComputedColumns.Add(DReader["name"].ToString());
}
}
After this, we enter the columns population operation - if the current column is not a computed column, then all the details of the current column is loaded.
The column size of a computed column depends on whether it is persisted or not. 'Persisted' as the name implies requires physical storage. So if this is a persisting computed column, then the column size returned by the SchemaOnly reader is put in place, otherwise it is 0.
Whether a computed column is a persisted one or not is checked in the following method. The working code is adapted from here.
private bool ThisIsAPersistedComputedColumn(string ColumnName)
{
using (SqlConnection Conn2 = new SqlConnection(Conn.ConnectionString))
{
Conn2.Open();
using (SqlCommand cmd = new SqlCommand
("SELECT t.name as TableName, c.name as ColumnName FROM sys.tables t
INNER JOIN sys.computed_columns c on c.object_id = t.object_id " +
"WHERE t.name = '" + TableName + "' AND c.is_persisted = 1", Conn2))
using (SqlDataReader DReader = cmd.ExecuteReader())
{
while (DReader.Read())
if (DReader["ColumnName"].ToString().Equals(ColumnName))
return true;
}
}
return false;
}
See that, the bit type "AllowDBNull" is changed to "Yes/No". The primary key, unique key, and foreign key columns are also changed to "Yes/No" according to their availability in the respective lists which we obtained earlier and passed to this method as parameters. Also see the increment of total size which is a very simple calculation. However, as a disclaimer, this is a very rough estimate, and the actual size of a record is very much likely to vary depending on the content of the record.
The Description, Example, RangeFrom, RangeTo members are populated by fixed values obtained from the App.config. Practically, the description should be edited by the user to elaborate the purpose of the column. However, this can be elaborated in the Notes column as well. It is up to the user where s/he chooses to elaborate.
The examples are loaded in two ways - according to the choice of user whether s/he wants to populate from the config file, or wants to populate from real data. This is being checked in this line:
Details.Example = ExampleChoiceParam == EXAMPLE_CHOICE.HardCoded ?
GetKeyValueFromAppConfig(Details.SqlDataType + "_Example") :
Details.Example = GetLiveExample(Details.ColumnName);
Loading Examples from the Config File
There are values in the configuration file that represent the description, example and ranges of SQL Server data types. The values are named according to the available sql data types. For example, for 'varchar' and 'real' types, we have the following entries in App.config. I have listed the types and descriptions from here. If you find a type and corresponding example, description and range is missing, you can simply add them in the App.config accordingly. This might be needed for future versions of SQL Server.
<add key="varchar_Example" value="Hello"/>
<add key="varchar_Description" value="Max characters"/>
<add key="real_Example" value="123"/>
<add key="real_RangeFrom" value="-3.40E + 38"/>
<add key="real_RangeTo" value="+3.40E + 38"/>
So it is easy to find a corresponding examples, ranges and descriptions of data types. For string types, the length is obtained from the columnsize, and put in place (using the first overload below).
private string GetKeyValueFromAppConfig(string Key, long ColumnSize)
{
string Description = ConfigurationManager.AppSettings[Key];
if (!string.IsNullOrEmpty(Description) && Description.Substring(0, 3).Equals("Max"))
Description = Description.Insert(4, ColumnSize.ToString() + " ");
return Description;
}
For examples and ranges, the second overload is called.
private string GetKeyValueFromAppConfig(string Key)
{
return ConfigurationManager.AppSettings[Key];
}
Loading Examples from Live Data
If the user chooses to populate the examples from live data, then the following method is called. It actually returns the first non-null value for the given column; that is the real data for the given column. A maximum of 50 characters are returned as examples might be quite lengthy. A point of interest here is, we have to use a second connection, as the first connection is being used by the calling method; we cannot work with the same connection.
private string GetLiveExample(string ColumnName)
{
using (SqlConnection Conn2 = new SqlConnection(Conn.ConnectionString))
{
Conn2.Open();
using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 " +
ColumnName + " FROM [" + TableName + "] WHERE " + ColumnName +
" IS NOT NULL", Conn2))
using (SqlDataReader DReader = cmd.ExecuteReader())
while (DReader.Read())
if (DReader[ColumnName].ToString().Length > 50)
return DReader[ColumnName].ToString().Substring(0, 50);
else
return DReader[ColumnName].ToString();
}
return "";
}
The example column is editable, so if the user is not happy with the populated examples, s/he is free to put down a suitable exmple.
Disk Space Calculation
It is very important to monitor the disk space in the database server computer so it doesn't run out of space. While this depends on many things including physical state of data and log files, this tool opts for providing a rough idea about the required space for a table.
After populating the columns, two methods of SQL server are used for the purpose. The following code shows the use of suggested method for physical statistics calculation (sys.dm_db_index_physical_stats) from Microsoft.
private string RetrieveDiskSpaceDetails()
{
StringBuilder SBuilder = new StringBuilder();
double NumRows = 0;
using (SqlCommand cmd = new SqlCommand("select *
from sys.dm_db_index_physical_stats(DB_ID(N'" + DBName + "'),
OBJECT_ID(N'" + TableName + "'), NULL, NULL , 'DETAILED');", Conn))
('" + TableName + "') with tableresults", Conn))
using (SqlDataReader DReader = cmd.ExecuteReader())
while (DReader.Read())
{
NumRows = Convert.ToInt32(DReader["record_count"]);
long TotalSpace = (long)(NumRows *
Convert.ToDouble(DReader["avg_record_size_in_bytes"]));
SBuilder.Append("Stats (acc. physical state) from [sys.dm_db_index_physical_stats]
:\n============================================================\n");
SBuilder.Append("Total records: " + NumRows + "." +
"\nAvg record size: " + DReader["avg_record_size_in_bytes"] + " bytes." +
"\nMin record size: " + DReader["min_record_size_in_bytes"] + " bytes." +
"\nMax record size: " + DReader["max_record_size_in_bytes"] + " bytes." +
"\nTotal Disk space occupied: " + TotalSpace + " bytes = " +
Math.Round((double)TotalSpace / 1024, 2) + " KB = " +
Math.Round((double)TotalSpace / (1024 * 1024), 4) + " MB.\n\n\n");
break;
}
...............................
...............................
The above method incorporates many factors for the calculation including pages and fragmentation. There is another built-in SP (sp_spaceused) for space calculation which can represent the actual allocated space for a table.
using (SqlCommand cmd = new SqlCommand("EXEC sp_spaceused N'" + TableName + "'", Conn))
using (SqlDataReader DReader = cmd.ExecuteReader())
while (DReader.Read())
{
double SpaceReserved = Convert.ToInt32(DReader["reserved"].ToString().Substring
(0, DReader["reserved"].ToString().Length - 3));
double DataSpace = Convert.ToInt32(DReader["data"].ToString().Substring
(0, DReader["data"].ToString().Length - 3)) * 1024;
double IndexSpace = Convert.ToInt32(DReader["index_size"].ToString().Substring
(0, DReader["index_size"].ToString().Length - 3)) * 1024;
SBuilder.Append("Stats (acc. reservation) from [sp_spaceused] SP:
\n============================================================\n");
if (NumRows == 0)
SBuilder.Append("Total allocated space (including index): " +
SpaceReserved + " bytes." +
"\nAvg record size (including index): 0 bytes." +
"\nAvg record size (excluding index): 0 bytes.");
else
SBuilder.Append("Total allocated space (inc. index): " + SpaceReserved + " KB." +
"\nAvg record size (inc. index): " +
Math.Round((DataSpace + IndexSpace) / NumRows, 2) + " bytes." +
"\nAvg record size (ex. index): " + Math.Round(DataSpace / NumRows, 2) +
" bytes.");
}
I am not a SQL guru, so I am not pretty sure if one method should be used over the other, or is there any environmental significance for applying a particular method in a particular environment. This version includes both of them so as to have a better overall idea about space requirements.
Following is a snap of the statistics retrieved by all three methods (please right click on it, copy and paste in mspaint to view a larger snap):
SortableDataDetailsListClass.cs
One of the difficulties with data grid is it doesn't allow sorting (by clicking on the column headers) if it is bound to a list. The workaround is to use custom a sortable list that implements the IComparer interface, and then re-bind the data grid with the sorted list. We pass the column and sorting order to the constructor.
private readonly string _memberName = string.Empty;
private readonly SortOrder _sortOrder = SortOrder.None;
public SortableDataDetailsListClass(string memberName, SortOrder sortingOrder)
{
_memberName = memberName;
_sortOrder = sortingOrder;
}
The Compare() method if IComparer actually performs the sorting by comparing the items in the list.
public int Compare(DataDetailsKeyCriteriaClass Details1, DataDetailsKeyCriteriaClass Details2)
{
if (_sortOrder != SortOrder.Ascending)
{
var tmp = Details1;
Details1 = Details2;
Details2 = tmp;
}
switch (_memberName)
{
case "ColumnName":
return Details1.ColumnName.CompareTo(Details2.ColumnName);
case "ColumnSize":
return Details1.ColumnSize.CompareTo(Details2.ColumnSize);
case "DataType":
return Details1.DataType.CompareTo(Details2.DataType);
....................................
....................................
case "Description":
if (Details1.Description == null || Details2.Description == null) return -1;
return Details1.Description.CompareTo(Details2.Description);
....................................
....................................
See this is not an efficient approach as we are hard-coding the column names. Also, we need a NULL check for the columns that might have NULLs (otherwise it will throw runtime exceptions). Definitely, we can opt for a generic approach.
The actual discussion of IComparer.Compare() method is beyond the scope and purpose of this article. So I shall not go deep into this. Just to summarise, this helps our data grid to sort the items.
SortableFKListClass.cs and SortablePKListClass.cs
These classes have the same purpose as the above class (SortableDataDetailsListClass.cs). Just that they are used to sort our primary keys and foreign keys lists.
ExcelOutputClass.cs
This class has a single method OutputToExcel() that is used for exporting our data grid to Excel. I shall not go through details of this part as there is plenty of workouts in the net about how to export data to Excel from a datagrid (see the references). It is just selecting a file for exporting, and saving it. I shall just highlight some formatting issues. The "Yes" cells are highlighted as green in the Excel file. This is done in the following code:
for (int i = 0; i < ColumnsGridView.Rows.Count; i++)
for (int j = 0; j < ColumnsGridView.Columns.Count; j++)
if (ColumnsGridView.Rows[i].Cells[j].Value != null)
{
worksheet.Cells[i + 2, j + 1] = ColumnsGridView.Rows[i].Cells[j].Value.ToString();
if (ColumnsGridView.Rows[i].Cells[j].
Value.ToString().Equals("Yes"))
worksheet.Cells[i + 2, j + 1].Interior.Color =
ColorTranslator.ToOle(Color.Green);
}
The header is formatted to be white font with gray back colour. This is done in the following code:
Microsoft.Office.Interop.Excel.Range formatRange;
formatRange = worksheet.get_Range("A1");
formatRange.EntireRow.Font.Bold = true;
formatRange.EntireRow.Interior.Color = ColorTranslator.ToOle(Color.Blue);
formatRange.EntireRow.Font.Color = ColorTranslator.ToOle(Color.White);
Finally cell width is set to Autofit so that everything is visible. This is done in the following code:
formatRange = worksheet.get_Range("A:" +
(char)(ColumnsGridView.Columns.Count - 1 + 'A'));
formatRange.Columns.AutoFit();
A sample output Excel file looks like the snap below:
Part 2: Loading Database and Tables
The program starts with connecting to the database. The connection string is specified in App.config. You need to change it to your database. Please note that we are specifying only the server name, no catalog (table) is there.
<connectionStrings>
<add name="DBConnectionString" connectionString="Data Source=.;
Integrated Security=true" providerName="System.Data.SqlClient" />
</connectionStrings>
The following code in the constructor lists all the databases of the server. The DB names are obtained from SYS.DATABASES.
Conn = new SqlConnection(ConfigurationManager.ConnectionStrings
["DBConnectionString"].ToString());
Conn.Open();
List<string> DBNames = new List<string>();
using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", Conn))
using (IDataReader DR = cmd.ExecuteReader())
{
while (DR.Read())
DBNames.Add(DR[0].ToString());
}
DatabasesCmb.DataSource = DBNames.OrderBy
(P => P.ToString()).ToList();
After the databases are loaded, we load all the tables belonging to the database in the display. We use the SqlConnectionStringBuilder class to modify our existing connection string to insert the catalog (i.e., table name) which we pick from the tables combo box. Then we obtain only the schema (i.e., structure of the table) from the connection using SqlClientMetaDataCollectionNames.Tables. Note that even we opt for tables only, this retrieves views as well. The table schema is loaded in our data table schema.
SqlConnectionStringBuilder SBuilder = new SqlConnectionStringBuilder
(ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString());
SBuilder.InitialCatalog = DatabasesCmb.Text;
Conn = new SqlConnection(SBuilder.ToString());
Conn.Open();
DataTable schema = Conn.GetSchema(SqlClientMetaDataCollectionNames.Tables);
List<string> TableNames = new List<string>();
foreach (DataRow row in schema.Rows)
if (row[3].Equals("BASE TABLE"))
TableNames.Add(row[2].ToString());
TablesCmb.DataSource = TableNames.OrderBy
(P => P.ToString()).ToList();
The schema contains four items as highlighted below. The TABLE_CATALOG is the name of the table or view, the TABLE_SCHEMA is the schema name, TABLE_NAME is self-explanatory, TABLE_TYPE is the type of the catalog (table/view).
Two example records of the schema table are shown below. See, the left one is a view, the right one is a table. Since we want to list tables only so we have an IF condition in the above code (if (row[3].Equals("BASE TABLE")).
At this point, we are done loading the database and tables.
Part 3: Creating the Dictionary
First, we list the unique keys of the table. This is a painless operation as these are simply obtainable from the schema. For this, we restrict the retrieval of indexes for the given table only, otherwise it would bring all the indexes under that database. Restrictions is an array of strings where the third index is the table name to restrict (for details of restrictions, please visit MSDN). This time, we use SqlClientMetaDataCollectionNames.IndexColumns to retrieve the index columns from the connection object (with applied restrictions).
string[] restrictions = new string[4];
restrictions[2] = TablesCmb.Text;
DataTable UniqueKeys =
Conn.GetSchema(SqlClientMetaDataCollectionNames.IndexColumns, restrictions);
List<string> UniqueIndexList = new List<string>();
foreach (DataRow UniqueKey in UniqueKeys.Rows)
UniqueIndexList.Add(UniqueKey["column_name"].ToString());
You can find unique columns of a table in SQL Server Management Studio (SSMS). If you open the property of an unique index, then you will see the unique columns there. For example, see the snap below:
The corresponding unique columns retrieved by our program is shown below. See it was successful in retrieving all the unique column (ApplicationId). In addition to what you get in SSMS, the primary key column (UserId) is by default a part of the unique columns.
Our list variables are the following four:
List<DataDetailsKeyCriteriaClass> ColumnList;
List<PKKeyCriteria> PKList;
List<FKKeyCriteria> FKList;
List<string> UniqueIndexList = new List<string>();
Next, we list the primary keys and foreign keys of the table. After successful listing, we list all the column details. Finally, we bind the lists to respective data grids.
PrimaryKeyClass PrimaryKeyObj = new PrimaryKeyClass(TablesCmb.Text, Conn);
PrimaryKeyObj.GetPrimaryKeysAndDependencies();
PKList = PrimaryKeyObj.PKList;
PKGridView.DataSource = PKList;
ForeignKeyClass ForeignKeyObj = new ForeignKeyClass(TablesCmb.Text, Conn);
ForeignKeyObj.GetForeignKeysAndDependencies();
FKList = ForeignKeyObj.FKList;
FKGridView.DataSource = FKList;
DataDetailsClass DataDetailsObj =
new DataDetailsClass(DatabasesCmb.Text, TablesCmb.Text, Conn);
if (ViewHardCodedExample.Checked)
DataDetailsObj.GetColumnDetails(PKList, FKList, UniqueIndexList,
out TotalSize, DataDetailsClass.EXAMPLE_CHOICE.HardCoded);
else if (ViewLiveExample.Checked)
DataDetailsObj.GetColumnDetails(PKList, FKList, UniqueIndexList,
out TotalSize, DataDetailsClass.EXAMPLE_CHOICE.Live);
ColumnList = DataDetailsObj.ColumnList;
ColumnsGridView.DataSource = ColumnList;
After binding, the cells with "Yes" are highlighted with green background colour. We did it in the CellFormatting() event of the grid.
private void ColumnsGridView_CellFormatting
(object sender, DataGridViewCellFormattingEventArgs e)
{
if (e.Value != null && e.Value.ToString().Equals("Yes"))
e.CellStyle.BackColor = Color.Green;
}
Sorting
This feature was missing in the first release; now it is available. To sort, just click on the column header of a data grid. We have ColumnHeaderMouseClick() event to handle the sorting as follows:
private void ColumnsGridView_ColumnHeaderMouseClick
(object sender, DataGridViewCellMouseEventArgs e)
{
SorGrid(ColumnsGridView.Columns[e.ColumnIndex], ColumnsGridView);
}
private void SorGrid(DataGridViewColumn Col, DataGridView GridView)
{
System.Windows.Forms.SortOrder sortOrder =
Col.HeaderCell.SortGlyphDirection == System.Windows.Forms.SortOrder.Ascending
? System.Windows.Forms.SortOrder.Descending
: System.Windows.Forms.SortOrder.Ascending;
switch (GridView.Name)
{
case "ColumnsGridView":
ColumnList.Sort(new SortableDataDetailsListClass(Col.Name, sortOrder)); break;
case "PKGridView":
PKList.Sort(new SortablePKListClass(Col.Name, sortOrder)); break;
case "FKGridView":
FKList.Sort(new SortableFKListClass(Col.Name, sortOrder)); break;
}
GridView.Refresh();
Col.HeaderCell.SortGlyphDirection = sortOrder;
}
It obtains a sort order, changes the glyph icon (up arrow/down arrow) and re-orders the current list with the corresponding sortable binding list. Finally, it refreshes the data grid.
Environment
The code was designed in Visual Studio 2015, with .NET Framework version 4.5. Tested on databases in SQL Server 2012.
Points of Interest
If there is a data adapter open in operation for a table, another data adapter cannot be run on the same table for operation using the same connection. Everything will be just fine if another SQL connection is used.
Discovered an interesting thing while connecting to SQL server using SQL authentication (UID, password). If the connection string is read from the configuration file, then after the first successful connection attempt, the password disappears from the connection object. So any further operation with the same connection object results in connection denial (because password is missing). To deal with such a problem, the connection string should be read again from the config file. I am not sure if this always happens.
Future Works
This tool can be substantially used and expanded to greater needs of knowing the database. One obvious field of improvement is the disk space calculation.
Incorporating the log file issues might be a good area of improvement.
Another thing might be to populate a combo box to load all the SQL server instances in the network (System.Data.Sql.SqlDataSourceEnumerator), and choosing the required one, rather than to change the config file each time. However, this will incur SQL authentication issues.
There is also opportunity to better handle connection timeout errors with a better msg and solution.
The limitation to work with administrative access only might lead to dissatisfaction, and can be worked upon to work with SQL authentication that doesn't have admin access. However, I highly doubt a success on this as it requires access to schema collection which is supposed to be available to the admins only.
Disclaimer
The tool failed to obtain legitimate keys from 'ReportServerTempDB' table. I am not sure why it is happening. Actually SP_FKEYS failed to list any primary keys in this database even if there are primary keys. May be it didn't care about this database as it is a temporary database (as the name suggests)? I shall try to find out the reason and post in subsequent history. Please keep an eye. Along with this, if you find the reason, please let it known to every body in the comments section. Any other question, advice, improvement is welcome, so please feel free to comment.
Disk space varies on many factors. The previous version used 'DBCC ShowContig' for disk space calculation. But according to Microsoft this will be deprecated in future versions. So the suggested method (sys.dm_db_index_physical_stats) is used in this release. Another method (SP: sp_spaceused) is used in parallel to obtain information about actually allocated disk space for a table. Just to acknowledge disk space cannot be measured very precisely, this just gives an overall rough estimate.
Another limitation of this project is it works with administrative access since it directly accesses the schema information using 'SqlClientMetaDataCollectionNames' with the connection object.
Acknowledgements
- Thanks to Paw Jershauge and SierraMike for the suggestions on disk space
- Thanks to Aaginor for reporting the SQL authentication bug
- Thanks to everybody for using the tool
Summary
The project opts for listing names, types, key types, key relations (references), descriptions, examples, ranges of all the columns of a SQL Server table, and total physical space occupied by a table at the moment of operation.
References
History
- 23rd September, 2016
- 25th Oct 2016
- Code refactoring - delegated tasks to classes
- Added facility for sorting on column header click (click on the header of the grids to sort the items), responsive design (to fit in different resolutions).
- 23rd Nov 2016
- Renovated disk space calculation, renovated examples loading from live data, brought in concerns for unicode and computed columns.
- 25th Nov 2016
- SQL Authentication bug fixing
- Change in interface
- Replaced deprecated '
DBCC ShowContig' with the latest 'sys.dm_db_index_physical_stats' - Change in interface snap
- One new snap added
- 1 old reference removed
- 2 new references added
- Change in sections - points of interest, disclaimer, disk space calculation, future works, acknowledgements, and references
