/// <summary>Implements the Exec method of the IDTCommandTarget interface. This is called when the command is invoked.</summary>
/// <param term='commandName'>The name of the command to execute.</param>
/// <param term='executeOption'>Describes how the command should be run.</param>
/// <param term='varIn'>Parameters passed from the caller to the command handler.</param>
/// <param term='varOut'>Parameters passed from the command handler to the caller.</param>
/// <param term='handled'>Informs the caller if the command was handled or not.</param>
/// <seealso class='Exec' />
public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled)
{
handled = false;
if (executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
{
//ext.Documents.Add();
//ext.Documents.Add(ext.ActiveDocument.Kind.ToString().Replace("{", "").Replace("}", ""));
//ext.WindowConfigurations.Add("test window");
//ext.ExecuteCommand("QueryDesigner.Select");
//ext.ExecuteCommand("QueryDesigner.SQL");
//ext.ExecuteCommand("QueryDesigner.ExecuteSQL");
if (commandName == "SSMSAddin.Connect.ViewReferencedValue")
{
var sqlParser = new SimpleSqlParser();
var ext = ((DTE2)ServiceCache.ExtensibilityModel);
IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
var window = (Window)ext.ActiveWindow;
var scriptWindow = _windowExtensions.ContainsKey(window) ? _windowExtensions[window] : null;
var values = scriptWindow?.GetSelectedValues();
foreach (var val in values.Where(v => v?.Values?.Count > 0))
{
var s = scriptFactory.CreateNewBlankScript(ScriptType.Sql, scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, null);
Document document = ext.ActiveDocument;
if (document != null)
{
var sql = scriptWindow.GetQuery();
var table = sqlParser.GetTableFromSql(sql, val.ColumnName);
var split = table.Split('.');
var schema = "dbo";
if (split.Length == 3)
{
schema = split[1].Replace("[", "").Replace("]", "");
table = split[2].Replace("[", "").Replace("]", "");
}
else if (split.Length == 2)
{
schema = split[0].Replace("[", "").Replace("]", "");
table = split[1].Replace("[", "").Replace("]", "");
}
else if (split.Length == 1)
{
table = split[0].Replace("[", "").Replace("]", "");
}
var sqlToGetReferences = $@"
SELECT S2.name AS SchemaName, T2.name AS TableName, C2.name AS ColumnName
FROM sys.tables T
JOIN sys.schemas S ON S.schema_id = T.schema_id
JOIN sys.columns C ON C.object_id = T.object_id
JOIN sys.foreign_key_columns FK ON FK.parent_object_id = T.object_id AND FK.parent_column_id = C.column_id
JOIN sys.tables T2 ON T2.object_id = FK.referenced_object_id
JOIN sys.columns C2 ON C2.object_id = T2.object_id AND C2.column_id = FK.referenced_column_id
JOIN sys.schemas S2 ON S2.schema_id = T2.schema_id
WHERE T.name = '{table}' AND S.name = '{schema}' AND C.name = '{val.ColumnName}'
";
var conInfo = scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo;
SqlCommand cmd = new SqlCommand(sqlToGetReferences, new SqlConnection($"Server={conInfo.ServerNameNoDot};Database={conInfo.AdvancedOptions["DATABASE"]};Trusted_Connection=True;"));
cmd.Connection.Open();
var referencedTable = string.Empty;
var referencedSchema = string.Empty;
var referencedColumn = string.Empty;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
referencedSchema = (string)reader["SchemaName"];
referencedTable = (string)reader["TableName"];
referencedColumn = (string)reader["ColumnName"];
}
}
cmd.Connection.Close();
//replace currently selected text
if (!string.IsNullOrEmpty(referencedSchema) && !string.IsNullOrEmpty(referencedTable) && !string.IsNullOrEmpty(referencedColumn))
{
TextSelection selection = (TextSelection)document.Selection;
selection.Insert($"SELECT * FROM [{referencedSchema}].[{referencedTable}] WHERE [{referencedColumn}] {val?.WhereClause()}", (Int32)EnvDTE.vsInsertFlags.vsInsertFlagsContainNewText);
ext.ExecuteCommand("Query.Execute");
}
}
}
handled = true;
return;
}
}
}
public void GetTableFromSql()
{
var sql = @"SELECT TOP 1000 [RowID]
,[SystemName]
,[SourceServerName]
,[SourceDBName]
,[SourceObjectName]
,[SourceSchemaName]
,[TargetServerName]
,[TargetDBName]
,[TargetObjectName]
,[TargetSchemaName]
,[DependencyFlg]
,[Sequence]
,[InactiveFlg]
,[SelectFieldNames]
,[InsertFieldNames]
,[OpenQueryFlg]
,[PrimaryKeyColumns]
,[PrimaryKeyDataTypes]
,[SchemaPrefixFlg]
,[IndexColumns1]
,[IndexColumns2]
,[PrimaryKeyFlg]
,[InsertIntoFlg]
,[TableHistoryFlg]
,[IdentityInsertFlg]
,[CreatedDate]
,[ModifiedDate]
,[ModifiedBy]
,[SourceKeyAttribute]
,[TargetKeyAttribute]
,[WhereClause]
,[DeleteFromDestUsingWhereFlg]
,[UseSSISBuilderFlg]
,[SSISOptionalODBCConnectionString]
,[RESTURL]
,[RESTURLAdditional]
,[RESTFileType]
,[RESTUser]
,[RESTPassword]
,[CreateClusteredColumnStoreIndexFlg]
,[ETLType]
,[FileDirectory]
,[FileExtension]
,[FormatFileDirectory]
,[FileLikeStatement]
,[FileFirstRow]
,[RebuildTableIndexes]
,[MergeSkipDeletesFlag]
,[FinalSchema]
,[SourceCredentialID]
,[SourceIsAzure]
,[TargetCredentialID]
,[TargetIsAzure]
FROM [ETLGen].[dbo].[tuETLObjectList]";
var ssp = new SimpleSqlParser();
var table = ssp.GetTableFromSql(sql, null);
Assert.AreEqual("[ETLGen].[dbo].[tuETLObjectList]".ToLower(), table);
}
