Click here to Skip to main content
13,145,464 members (48,163 online)
Click here to Skip to main content
Add your own
alternative version

Stats

36.8K views
1.7K downloads
60 bookmarked
Posted 22 Sep 2016

Data Dictionary in C#.NET

, 24 Nov 2016
Rate this:
Please Sign up or sign in to vote.
Create a data dictionary for your database tables

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)     // Only the three columns are editable, others are read-only.
    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; }  // The primary key column name of a table.
    public string ForeignKeyTable { get; set; }     // The table where this primary key is used as a foreign key.
    public string NameInForeignKeyTable { get; set; }   // The column name that the foreign table uses for this primary key.
}
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.

// Soemtimes dependencies might not be obtained for primary keys from the above method. Following is a workaround. However, we don't have
// any information about the foreign table and column name in such cases.
if (PKList.Count == 0)
    using (var cmd = new SqlCommand("SELECT * FROM [" + TablesCmb.Text + "]", Conn))
    {
        DAdapter = new SqlDataAdapter();
        DAdapter.SelectCommand = cmd;
        DAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;    // Optional, but as a safety measure.

        var dtab = new DataTable();
        DAdapter.FillSchema(dtab, SchemaType.Source); // Only the schema.

        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; }  // The foreign key column name of a table.
    public string PrimaryKeyTable { get; set; }     // The table where this foreign key is used as a primary key.
    public string NameInPrimaryKeyTable { get; set; }   // The name that the table uses this column as a primary key.
}
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 retuns 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();   // Get the column meta-data only.

        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) // If the current column is not a computed column.
            {
                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)) // If only the computed column is a persisted one, then it would occupy disk space.
                    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;   // The computed column is listed as a persisted column.
        }
    }
    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, we 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)
{
    // If the data type is not found, then it returns null, which ultimately displays blank.
    string Description = ConfigurationManager.AppSettings[Key];

    // If a description is found, and it is a description for string types (determined by 'Max' at the beginning), then insert the size.
    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]; // If the data type is not found, then it returns null, which ultimately displays blank.
}

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())  // Shortening examples - string examples can be quite large.
                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. 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;
    // http://www.sqlserver-dba.com/2013/07/calculate-sql-row-size.html
    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))
    //using (SqlCommand cmd = new SqlCommand("dbcc showcontig ('" + 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));  // In KB.
        double DataSpace = Convert.ToInt32(DReader["data"].ToString().Substring(0, DReader["data"].ToString().Length - 3)) * 1024;  // In bytes; data is in KB.
        double IndexSpace = Convert.ToInt32(DReader["index_size"].ToString().Substring(0, DReader["index_size"].ToString().Length - 3)) * 1024;  // In bytes; data is in KB.

        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; // the member name to be sorted
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:

// storing Each row and column value to excel sheet
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"))    // Back colour of "Yes" cells to green.
                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:

// formatting the header.
Microsoft.Office.Interop.Excel.Range formatRange;
formatRange = worksheet.get_Range("A1");
formatRange.EntireRow.Font.Bold = true;
formatRange.EntireRow.Interior.Color = ColorTranslator.ToOle(Color.Blue);   // Header backcolour to blue.
formatRange.EntireRow.Font.Color = ColorTranslator.ToOle(Color.White);      // Header forecolour to 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'));  // The range of columns.
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();  // Sort the items alphabetically and display.

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"))    // Only add tables, not views.
        TableNames.Add(row[2].ToString());
TablesCmb.DataSource = TableNames.OrderBy(P => P.ToString()).ToList();  // Sort the items alphabetically and display.

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

// Specify the 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)
{
    // Code adapted from http://stackoverflow.com/questions/16105718/datagridview-changing-cell-background-color
    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;  // Display up arrow or down arrow.

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 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.

he 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

Thanking Paw Jershauge and SierraMike for the suggestions on disk space.

Thanking 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

Schema Restrictions

Primary Keys without References

31 columns from GetSchemaTable()

ForeignKeys and Dependencies

Excel Output Reference 1

Excel Output Reference 2

Excel Output Reference 3

Excel Output Reference 4

Excel Output Reference 5

Setting GridView Cell BackColor

Sortable Binding List

A Responsive Design Technique for WinForms

List all the computed columns of a table

Check if a computed column is a persisted one or not

Allocated space information

Physical space information

History

23 Sep 2016: First release.

25 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).

23 Nov 2016: Renovated disk space calculation, renovated examples loading from live data, brought in concerns for unicode and computed columns.

25 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mehedi Shams
Software Developer
Bangladesh Bangladesh
A software developer mainly in .NET technologies and SQL Server. Love to code and learn.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionWere you just bored? Pin
Member 1150840629-Nov-16 15:48
memberMember 1150840629-Nov-16 15:48 
AnswerRe: Were you just bored? Pin
Mehedi Shams29-Nov-16 17:30
memberMehedi Shams29-Nov-16 17:30 
GeneralRe: Were you just bored? Pin
Member 1150840629-Nov-16 18:00
memberMember 1150840629-Nov-16 18:00 
GeneralRe: Were you just bored? Pin
Mehedi Shams29-Nov-16 18:29
memberMehedi Shams29-Nov-16 18:29 
GeneralRe: Were you just bored? Pin
Member 1150840629-Nov-16 18:40
memberMember 1150840629-Nov-16 18:40 
GeneralRe: Were you just bored? Pin
Mehedi Shams29-Nov-16 19:07
memberMehedi Shams29-Nov-16 19:07 
GeneralRe: Were you just bored? Pin
Member 1150840629-Nov-16 22:34
memberMember 1150840629-Nov-16 22:34 
GeneralRe: Were you just bored? Pin
Mehedi Shams30-Nov-16 0:19
memberMehedi Shams30-Nov-16 0:19 
QuestionThanks for your share, but I got the below error Pin
winsonet29-Nov-16 14:59
memberwinsonet29-Nov-16 14:59 
AnswerRe: Thanks for your share, but I got the below error Pin
Mehedi Shams29-Nov-16 17:09
memberMehedi Shams29-Nov-16 17:09 
GeneralRe: Thanks for your share, but I got the below error Pin
winsonet29-Nov-16 17:21
memberwinsonet29-Nov-16 17:21 
GeneralRe: Thanks for your share, but I got the below error Pin
Mehedi Shams29-Nov-16 17:44
memberMehedi Shams29-Nov-16 17:44 
GeneralRe: Thanks for your share, but I got the below error Pin
winsonet29-Nov-16 19:23
memberwinsonet29-Nov-16 19:23 
GeneralRe: Thanks for your share, but I got the below error Pin
Mehedi Shams30-Nov-16 0:21
memberMehedi Shams30-Nov-16 0:21 
GeneralRe: Thanks for your share, but I got the below error Pin
winsonet30-Nov-16 4:31
memberwinsonet30-Nov-16 4:31 
QuestionSchema needed? Pin
MoscowFlyer25-Nov-16 4:00
memberMoscowFlyer25-Nov-16 4:00 
AnswerRe: Schema needed? Pin
Mehedi Shams25-Nov-16 18:25
memberMehedi Shams25-Nov-16 18:25 
GeneralRe: Schema needed? Pin
Member 781193529-Nov-16 6:48
memberMember 781193529-Nov-16 6:48 
GeneralRe: Schema needed? Pin
Mehedi Shams29-Nov-16 14:11
memberMehedi Shams29-Nov-16 14:11 
QuestionThanks for the article Pin
Member 1097080824-Nov-16 22:36
professionalMember 1097080824-Nov-16 22:36 
AnswerRe: Thanks for the article Pin
Mehedi Shams25-Nov-16 18:22
memberMehedi Shams25-Nov-16 18:22 
GeneralExcellent article Pin
robertrevolver24-Nov-16 8:55
professionalrobertrevolver24-Nov-16 8:55 
GeneralRe: Excellent article Pin
Mehedi Shams24-Nov-16 11:54
memberMehedi Shams24-Nov-16 11:54 
QuestionLogin with SQL-Authentication doesn't work Pin
Aaginor24-Nov-16 3:59
memberAaginor24-Nov-16 3:59 
AnswerRe: Login with SQL-Authentication doesn't work Pin
Mehedi Shams24-Nov-16 11:56
memberMehedi Shams24-Nov-16 11:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 25 Nov 2016
Article Copyright 2016 by Mehedi Shams
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid