382 lines
10 KiB
C#
382 lines
10 KiB
C#
<Query Kind="Program">
|
|
<Reference><ProgramFilesX64>\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 |