Know Your Database Metadata With C# Code

In this blog, we will see some tricks to analyze the database metadata.

If you are working on some project where a database is needed, at some point in time, you need to analyze the Metadata of the database for performance issues or for other concerns.

In this blog, we will see some tricks to analyze the database metadata.

1. Get all the database names.
2. Get all the table names reside in the database.
3. Get the header for all the tables.
4. Get all the database names and size.
5. Get all the table names with row count.

I am using C# code for getting this data and using MS SQL Server.

Get all the database names

Run this query on the master database.

First, I am writing an SQL query for this. Then, I’ll show you the C# code:

SELECT * FROM sys.databases

This gives you all the databases and additional info about those databases.

C# code for this query -

public Void GetDatabaseList(string conString) {
    using(SqlConnection con = new SqlConnection(conString)) {
        con.Open();
        using(SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con)) {
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read()) {
                Console.WriteLine(rdr[0]);
            }
        }
    }
}

Get all the table names residing in the database

For getting all the tables from a database. (Query on particular database)

SQL

1. To get all the table names from a database
   

   SELECT name FROM sys.Tables

2. To get only those tables which are having a particular column
   

   SELECT name
   FROM   sys.tables
   WHERE  Col_length(name, 'xyz') -- here xyz is your column name

C# Code

public List < string > GetDatabaseList(string conString) {
    List < string > list = new List < string > ();
    using(SqlConnection con = new SqlConnection(conString)) {
        con.Open();
        using(SqlCommand cmd = new SqlCommand("SELECT name from sys.databases", con)) { //List<string> tables = new List<string>();
            DataTable dt = con.GetSchema("Tables");
            foreach(DataRow row in dt.Rows) {
                string tablename = (string) row[1] + "." + (string) row[2];
                list.Add(tablename);
            }
        }
    }
    return list;
}

Get the header for all the tables

If you want to get the header of tables, the given code will help you. I don’t have any direct SQL query for this.

C# code

private IEnumerable < string > GetColumnNames(string conStr, string tableName) {
    var result = new List < string > ();
    using(var sqlCon = new SqlConnection(conStr)) {
        sqlCon.Open();
        var sqlCmd = sqlCon.CreateCommand();
        sqlCmd.CommandText = "select * from " + tableName + " where 1=0"; // No data wanted, only schema
        sqlCmd.CommandType = CommandType.Text;
        var sqlDR = sqlCmd.ExecuteReader();
        var dataTable = sqlDR.GetSchemaTable();
        foreach(DataRow row in dataTable.Rows) result.Add(row.Field < string > ("ColumnName"));
    }
    return result;
}

Here, we are fetching the schema of table and extracting the column names from that.

Get all the database names and size

SQL query

WITH fs
     AS (SELECT database_id,
                type,
                size * 8.0 / 1024 size
         FROM   sys.master_files)
SELECT NAME,
       (SELECT Sum(size)
        FROM   fs
        WHERE  type = 0
               AND fs.database_id = db.database_id) DataFileSizeMB,
       (SELECT Sum(size)
        FROM   fs
        WHERE  type = 1
               AND fs.database_id = db.database_id) LogFileSizeMB
FROM   sys.databases db

C# code

public void GetDatabaseListwithsizeinMB(string conString) {
        using(SqlConnection con = new SqlConnection(conString)) {
                con.Open();
                using(SqlCommand cmd = new SqlCommand(@ "with fs
                        as(select database_id, type, size * 8.0 / 1024 size from sys.master_files) select name, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB from sys.databases db ", con)) {
                        using(IDataReader dr = cmd.ExecuteReader()) {
                            while (dr.Read()) {
                                Console.WriteLine(dr[0] + " " + dr[1] + " " + dr[2]);
                            }
                        }
                    }
                }

Get all the table names with row count

SQL Query

DECLARE @QueryString NVARCHAR(max);

SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ', '')
                      + 'SELECT ' + ''''
                      + Quotename(Schema_name(sOBJ.schema_id))
                      + '.' + Quotename(sOBJ.NAME) + ''''
                      + ' AS [TableName] , COUNT(*) AS [RowCount] FROM '
                      + Quotename(Schema_name(sOBJ.schema_id))
                      + '.' + Quotename(sOBJ.NAME) + ' WITH (NOLOCK) '
FROM   sys.objects AS sOBJ
WHERE  sOBJ.type = 'U'
       AND sOBJ.is_ms_shipped = 0x0
ORDER  BY Schema_name(sOBJ.schema_id),
          sOBJ.NAME;

EXEC Sp_executesql
  @QueryString

C# Code

public void GetallthetablenameswithrowcountNumber string conString) {
    using(SqlConnection con = new SqlConnection(conString)) {
        con.Open();
        using(SqlCommand cmd = new SqlCommand(@ "DECLARE @QueryString NVARCHAR(MAX) ;
                SELECT @ QueryString = COALESCE(@QueryString + ' UNION ALL ', '') + 'SELECT ' + ''
                '' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) + ''
                '' + ' AS [TableName], COUNT( * ) AS[RowCount] FROM ' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
                FROM sys.objects AS sOBJ WHERE sOBJ.type = 'U'
                AND sOBJ.is_ms_shipped = 0x0 ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name; EXEC sp_executesql @ QueryString ", con)) {
                using(IDataReader dr = cmd.ExecuteReader()) {
                    while (dr.Read()) {
                        Console.WriteLine(dr[0] + " " + dr[1]);
                    }
                }
            }
        }
    }

Apart from all the above, if you want to have all the tables and table headers in a CSV file, I wrote the code for this.

Output Format

Tablename
	Columnheader1	Columnheader2	Columnheader3

Here is my GitHub repo where you can find the project: MetaDataofyourDatabase