<Query Kind="Program">
  <Reference>&lt;ProgramFilesX64&gt;\Microsoft SDKs\Azure\.NET SDK\v2.9\bin\plugins\Diagnostics\Newtonsoft.Json.dll</Reference>
  <Namespace>Newtonsoft.Json</Namespace>
  <Namespace>Newtonsoft.Json.Converters</Namespace>
  <Namespace>Newtonsoft.Json.Linq</Namespace>
  <Namespace>Newtonsoft.Json.Schema</Namespace>
  <Namespace>Newtonsoft.Json.Serialization</Namespace>
  <Namespace>System</Namespace>
  <Namespace>System.Diagnostics</Namespace>
  <Namespace>System.Diagnostics.Tracing</Namespace>
  <Namespace>System.Dynamic</Namespace>
  <Namespace>System.IO.Compression</Namespace>
  <Namespace>System.Threading.Tasks</Namespace>
</Query>

#region *** Enumerations ***

public enum QueryRegions
{
	Production,
	Training,
	Oak,
	Dev
}

#endregion

#region *** Options ***

public static string crossDBQueryPath = @"D:\CrossDBQueries";
public static QueryRegions queryRegion = QueryRegions.Production;
public static bool dumpSmcSql = false;
public static bool dumpDBList = true;
public static bool dumpQueries = false;
public static string[] appVersions = {

};
public static string[] serverExclusions = {
	@"paonsql19.sdt.local\instance1",
	@"paonsql17.sdt.local\instance1",
	@"p0555-sql-01.sdt.local\instance1",
	"P0000-sql-01.sdt.local",
	"P9900-SQL-03.sdt.local",
	"P9900-sql-05.sdt.local"
};
public static string[] targetServers = {
};
public static string[] targetDBs = {
	//"jazz prd Gillette Childrens 20150417.1"
	//"jazz prd Southern Illinois 20140408.1"
	//"jazz tst Southern Illinois 20190918.1"
	//"jazz dev master Prod"
};
#endregion *** Options ***

#region *** SMC Settings and queries ***

public static string smcSql => @"SELECT di.ServerName, di.DatabaseGUID, di.DatabaseName, di.PhysicalName, di.OrgPin, di.AppVersion, di.Description
FROM [dbo].[viewDatabaseInfo] di
WHERE 1=1"
+ (queryRegion == QueryRegions.Dev
? @"
  AND di.PhysicalName like 'jazz%'"
: $@"
  AND di.PhysicalName like 'jazz {(queryRegion != QueryRegions.Production ? "tst" : "prd")}%'
  AND di.ServerName like 'p%'") 
+ (serverExclusions.Any()
? $@"
  AND di.ServerName not in ('{string.Join("', '", serverExclusions)}')"
: "") 
+ (targetServers.Any()
? $@"
  AND di.ServerName in ('{string.Join("', '", targetServers)}')"
: "")
+ (targetDBs.Any()
? $@"
  AND di.PhysicalName IN ('{string.Join(", '", targetDBs)}')
"
: "")
+ (appVersions.Any()
? $@"
  AND di.AppVersion in ('{string.Join("', '", appVersions)}')"
: "");
public static string smcServer { get; set; }
public static string smcDB { get; set; }

#endregion

#region *** Program variables and settings ***

public static int maxThreads = 8;
public static int timeoutInMinutes = 2;
public static IList<CrossDBQueryResult> crossDBQueryResults = new List<CrossDBQueryResult>();
public static IOrderedEnumerable<Database> databases => GetDatabases();

#endregion

void Main()
{
	databases
	.GroupBy(db => db.AppVersion, (grp, data) => data.Select(x => x.AppVersion).First())
	.OrderByDescending(db => db)
	.Dump("AppVersions");
	/*
		databases
		.Select(db => new
		{
			db.AppVersion,
			db.PhysicalName,
			OrgPin = $"'{db.OrgPin}'" })
		.Where(db => db.AppVersion != "2019.43")	
		.OrderByDescending(db => db.AppVersion)
		.ThenBy(db => db.OrgPin)
		.Dump("AppVersions");
	*/

	//	ExecuteCrossDBQueries()
//	//.Where(r => !r.Exceptions.Any() && (r.Results.First().Results.First().Count() != 2).Any())
//	.OrderBy(dbqr => dbqr.Results.Any() ? 0 : 1)
//	.ThenBy(dbqr => dbqr.Database.DatabaseName).Dump(nameof(crossDBQueryResults));
}

#region *** Other classes ***

public class CrossDBQueryResult
{
	public Database Database { get; set; }
	public List<SqlResults> Results { get; set; }
	public List<string> Exceptions { get; set; }
	public CrossDBQueryResult()
	{
		Results = new List<SqlResults>();
		Exceptions = new List<string>();
	}

	public CrossDBQueryResult(Database database, string name, IEnumerable<dynamic> enumerable)
	: this()
	{
		Database = database;
		Results.Add(new SqlResults(name, enumerable));
	}

	public CrossDBQueryResult(Database database, Exception exception)
	: this()
	{
		Database = database;
		Exceptions.Add(exception.ExceptionMessage());
	}

	object ToDump() => Exceptions.Any() ? (object)new
	{
		Database,
		Results,
		Exceptions
	} : new
	{
		Database,
		Results
	};
}

public class SqlResults
{
	public string Name { get; set; }
	public IEnumerable<dynamic> Results { get; set; }
	public SqlResults(string name, IEnumerable<dynamic> results)
	{
		Name = name;
		Results = results;
	}
}

public static class ExceptionExtension
{
	public static string ExceptionMessage(this Exception exception)
	{
		return $@"{exception.Message}{(exception.InnerException != null
		? $@"
		{"".PadLeft(40, '-')}
		{exception.InnerException.ExceptionMessage()}"
		: "")}";
	}
}

public class Database
{
	public string Server { get; set; }
	public Guid DatabaseGuid { get; set; }
	public string DatabaseName { get; set; }
	public string PhysicalName { get; set; }
	public string OrgPin { get; set; }
	public string AppVersion { get; set; }
	public string Description { get; set; }

	public Database()
	{

	}

	public Database(DataRow dr)
	{
		Server = dr.Field<string>("ServerName");
		DatabaseGuid = dr.Field<Guid>("DatabaseGuid");
		DatabaseName = dr.Field<string>("DatabaseName");
		PhysicalName = dr.Field<string>("PhysicalName");
		OrgPin = dr.Field<string>("OrgPin");
		AppVersion = dr.Field<string>("AppVersion");
		Description = dr.Field<string>("Description");
	}
}

public class ThreadWithState
{
	private string connectionString => BuildConnectionString(_server, _databaseName);
	private Database _database;
	private string _server => _database?.Server ?? string.Empty;
	private string _databaseName => _database?.PhysicalName ?? string.Empty;
	private string sqlName;
	private IEnumerable<string> _sqlQueries;

	public ThreadWithState(Database database, KeyValuePair<string, IEnumerable<string>> sqlqueries)
	{
		_database = database;
		sqlName = sqlqueries.Key;
		_sqlQueries = sqlqueries.Value;
	}

	public void ThreadProc()
	{
		var crossDB = crossDBQueryResults.FirstOrDefault(x => x.Database == _database);
		IEnumerable<dynamic> ThreadResults;
		foreach (var sqlQuery in _sqlQueries)
		{
			try
			{
				ThreadResults = SelectDynamic(connectionString, sqlQuery);
				var result = ThreadResults.First();
				if (crossDB != null)
				{
					crossDB.Results.Add(new SqlResults(sqlName, ThreadResults));
				}
				else
				{
					crossDB = new CrossDBQueryResult(_database, sqlName, ThreadResults);
					crossDBQueryResults.Add(crossDB);
				}
			}
			catch (Exception ex)
			{
				if (crossDB != null)
				{
					crossDB.Exceptions.Add(new Exception(sqlQuery, ex).ExceptionMessage());
				}
				else
				{
					crossDBQueryResults.Add(new CrossDBQueryResult(_database, new Exception(sqlQuery, ex)));
				}
			}
		}
	}
}

#endregion

#region *** Other methods here ***

public static IOrderedEnumerable<Database> GetDatabases()
{
	switch (queryRegion)
	{
		case QueryRegions.Production:
			smcServer = "ioak-sql-smc-01";
			smcDB = "smc prod";
			break;
		case QueryRegions.Training:
			smcServer = "ioak-sql-smc-01";
			smcDB = "smc training";
			break;
		case QueryRegions.Oak:
			smcServer = "ioak-sql-smc-01.sdt.local";
			smcDB = "smc qa";
			break;
		default:
			smcServer = "DDEN-SQL-SMC-01.sdt.local";
			smcDB = "smc dev";
			break;
	}
	if (dumpSmcSql) smcSql.Dump(nameof(smcSql));
	IEnumerable<Database> dbInfos = new List<UserQuery.Database>();
	var connStr = BuildConnectionString(smcServer, smcDB);
	using (var conn = new SqlConnection(connStr))
	{
		conn.Open();
		var command = new SqlCommand(smcSql, conn);
		command.CommandType = CommandType.Text;
		var table = new DataTable();
		using (SqlDataReader reader = command.ExecuteReader())
		{
			table.BeginLoadData();
			table.Load(reader);
			table.EndLoadData();
			conn.Close();
		}
		dbInfos = table.AsEnumerable().Select(dr => new Database(dr));
	}
	return dbInfos
	.OrderBy(d => d.Server).ThenBy(d => d.PhysicalName);
}

public Dictionary<string, IEnumerable<string>> GetUserQueries(string path = null)
{
	var grp = 0;
	return Directory.GetFiles(path ?? Directory.GetCurrentDirectory(), "*.sql", SearchOption.TopDirectoryOnly)
	.ToDictionary(d => Path.GetFileNameWithoutExtension(d), f => File.ReadAllLines(f)
	.Select(r =>
	{
		if (r.Equals("go", StringComparison.CurrentCultureIgnoreCase))
		{
			r = "";
			return new { grp = grp++, r };
		}
		else
		{
			return new { grp, r };
		}
	}).GroupBy(r => r.grp, (x, lines) => string.Join(" ", lines.Select(l => l.r).Where(l => !string.IsNullOrEmpty(l)))));
}

public static IEnumerable<dynamic> SelectDynamic(string connStr, string sql)
{
	using (var conn = new SqlConnection(connStr))
	{
		conn.Open();
		var command = new SqlCommand(sql, conn);
		command.CommandType = CommandType.Text;
		command.CommandTimeout = (int)TimeSpan.FromMinutes(timeoutInMinutes).TotalSeconds;
		using (var reader = command.ExecuteReader())
		{
			var names = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
			foreach (IDataRecord record in reader as IEnumerable)
			{
				var expando = new ExpandoObject() as IDictionary<string, object>;
				foreach (var name in names)
					expando[name] = record[name];

				yield return expando;
			}
		}
		conn.Close();
	}
}

public IList<CrossDBQueryResult> ExecuteCrossDBQueries()
{
	if (dumpDBList) databases.Dump(nameof(databases));
	var queries = GetUserQueries(crossDBQueryPath);
	if (dumpQueries) queries.Dump(nameof(queries));
	RunCrossDBQuery(databases, queries);
	return crossDBQueryResults;
}

public void RunCrossDBQuery(IEnumerable<Database> databases, Dictionary<string, IEnumerable<string>> sqlList)
{
	var stopWatch = DateTime.Now;
	var query = databases.AsParallel()
	.SelectMany(database => sqlList.Where(sql => sql.Value.Any()).AsParallel()
	.Select(sql => new ThreadWithState(database, sql)));
	foreach (var q in query)
	{
		q.ThreadProc();
	}
	DateTime.Now.Subtract(stopWatch).TotalSeconds.Dump(nameof(stopWatch));
}

public static string BuildConnectionString(string server, string database, string username = null, string password = null)
{
	return username == null
	? $"data source={server};initial catalog='{database}';persist security info=True;Integrated Security=SSPI;"
	: $"data source={server};initial catalog='{database}';User ID={username};pwd={password}";
}

#endregion