SQL Server 查詢所有資料庫的資料欄位首筆資料

1. 說明

---

筆記 SQL Server 如何透過 .NET Console 使用 Dapper 查詢所有資料庫的資料欄位首筆資料。

說明

public class ColumnInfo
{
    public string SchemaName { get; set; }
    public string TableName { get; set; }
    public string ColumnName { get; set; }
    public string DataType { get; set; }
}

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=ServerName;Database={dbname};Integrated Security=True;TrustServerCertificate=True;";

static string GetTopColumnName(string connectionString, string schemaName, string tableName, string columnName)
{
    using var connection = new SqlConnection(connectionString);
    string query = $@"SELECT TOP 1 [{columnName}] FROM [{schemaName}].[{tableName}]";
    return connection.QueryFirstOrDefault<string>(query);
}

static void ProcessDBData(string dbName, string conn)
{
    conn = conn.Replace("{dbname}", dbName);
    using var connection = new SqlConnection(conn);
    string query = @"
            SELECT 
                s.name AS SchemaName,
                t.name AS TableName,
                c.name AS ColumnName,
                ty.name AS DataType
            FROM 
                sys.schemas AS s
            JOIN 
                sys.tables AS t ON s.schema_id = t.schema_id
            JOIN 
                sys.columns AS c ON t.object_id = c.object_id
            JOIN 
                sys.types AS ty ON c.user_type_id = ty.user_type_id
            ORDER BY 
                s.name, t.name, c.column_id;";

    var columnInfos = connection.Query<ColumnInfo>(query).ToList();

    using StreamWriter writer = new StreamWriter("result.txt", true);
    
    foreach (var columnInfo in columnInfos)
    {
        string columnDetails = $"[{dbName}].[{columnInfo.SchemaName}].[{columnInfo.TableName}].[{columnInfo.ColumnName}], Data Type: {columnInfo.DataType}";
        writer.WriteLine("\n" + columnDetails);

        try
        {
            string topColumnValue = GetTopColumnName(conn, columnInfo.SchemaName, columnInfo.TableName, columnInfo.ColumnName);
            writer.WriteLine(topColumnValue);
        }
        catch (Exception)
        {
            Console.WriteLine($"FAILED: {columnDetails}");
        }
    }

    Console.WriteLine($"{dbName} Done");
}

var databases = new List<string>
    {
        "dbname1", "dbname2", "dbname3",
    };

databases.ForEach(db => ProcessDBData(db, connectionString));

初略的程式碼，還有許多優化的地方，例如目前 DBName 使用 SELECT * FROM sys.databases 取得，但可以改為直接在程式碼當中去查詢。