I noticed recently that I can't seem to search my SQL queries using Util.GetMyQueries() even though the checkbox for searches includes SQL queries. Is this by design or am I missing something?
It took me a bit, but I have to apologize for even posting this question without digging just a tad deeper. I was able to look at some of the scripts saved in LINQPad as opposed to those I've saved in SSMS and saw the initial XML that marked it. I'm assuming that Util.GetMyQueries() only looks at .linq files and digs a bit deeper to get the contents. Here's the script (simplistic and borrowing heavily from MSDN examples on recursively searching a directory) that I used to change my .SQL scripts into .LINQ so I can now search through all of them as needed. Now if only I could do something similar with the (relatively few) PowerShell scripts!
public class RecursiveFileSearch
{
static System.Collections.Specialized.StringCollection log = new System.Collections.Specialized.StringCollection();
static void Main()
{
DirectoryInfo rootDir = new DirectoryInfo("c:\\temp");
WalkDirectoryTree(rootDir);
// }
// Write out all the files that could not be processed.
Console.WriteLine("Files with restricted access:");
foreach (string s in log)
{
Console.WriteLine(s);
}
// Keep the console window open in debug mode.
// Console.WriteLine("Press any key");
// Console.ReadKey();
}
static void WalkDirectoryTree(System.IO.DirectoryInfo root)
{
System.IO.FileInfo[] files = null;
System.IO.DirectoryInfo[] subDirs = null;
// First, process all the files directly under this folder
try
{
files = root.GetFiles("*.*");
ChangeFiles(root.FullName);
}
// This is thrown if even one of the files requires permissions greater
// than the application provides.
catch (UnauthorizedAccessException e)
{
// This code just writes out the message and continues to recurse.
// You may decide to do something different here. For example, you
// can try to elevate your privileges and access the file again.
log.Add(e.Message);
}
catch (System.IO.DirectoryNotFoundException e)
{
Console.WriteLine(e.Message);
}
if (files != null)
{
foreach (System.IO.FileInfo fi in files.Where(wh => wh.Extension == "sql"))
{
// In this example, we only access the existing FileInfo object. If we
// want to open, delete or modify the file, then
// a try-catch block is required here to handle the case
// where the file has been deleted since the call to TraverseTree().
fi.FullName.Dump();
}
// Now find all the subdirectories under this directory.
subDirs = root.GetDirectories();
foreach (System.IO.DirectoryInfo dirInfo in subDirs)
{
// Resursive call for each subdirectory.
WalkDirectoryTree(dirInfo);
}
}
}
static void ChangeFiles(string currentDirName)
{
char[] buffer = new char[10000];
string[] files = System.IO.Directory.GetFiles(currentDirName, "*.sql");
// files.Dump();
foreach (string fileName in files)
{
string.Format("Working on {0}", fileName).Dump();
FileInfo file = new FileInfo(fileName);
string renamedFile = file.FullName + ".orig";
File.Move(file.FullName, renamedFile);
using (StreamReader sr = new StreamReader(renamedFile))
using (StreamWriter sw = new StreamWriter(file.FullName, false))
{
sw.Write("<Query Kind=\"SQL\">\r\n</Query >\r\n ");
int read;
while ((read = sr.Read(buffer, 0, buffer.Length)) > 0)
sw.Write(buffer, 0, read);
}
File.Move(file.FullName, file.FullName + ".linq");
File.Delete(renamedFile);
}
}
}
Comments
public class RecursiveFileSearch { static System.Collections.Specialized.StringCollection log = new System.Collections.Specialized.StringCollection(); static void Main() { DirectoryInfo rootDir = new DirectoryInfo("c:\\temp"); WalkDirectoryTree(rootDir); // } // Write out all the files that could not be processed. Console.WriteLine("Files with restricted access:"); foreach (string s in log) { Console.WriteLine(s); } // Keep the console window open in debug mode. // Console.WriteLine("Press any key"); // Console.ReadKey(); } static void WalkDirectoryTree(System.IO.DirectoryInfo root) { System.IO.FileInfo[] files = null; System.IO.DirectoryInfo[] subDirs = null; // First, process all the files directly under this folder try { files = root.GetFiles("*.*"); ChangeFiles(root.FullName); } // This is thrown if even one of the files requires permissions greater // than the application provides. catch (UnauthorizedAccessException e) { // This code just writes out the message and continues to recurse. // You may decide to do something different here. For example, you // can try to elevate your privileges and access the file again. log.Add(e.Message); } catch (System.IO.DirectoryNotFoundException e) { Console.WriteLine(e.Message); } if (files != null) { foreach (System.IO.FileInfo fi in files.Where(wh => wh.Extension == "sql")) { // In this example, we only access the existing FileInfo object. If we // want to open, delete or modify the file, then // a try-catch block is required here to handle the case // where the file has been deleted since the call to TraverseTree(). fi.FullName.Dump(); } // Now find all the subdirectories under this directory. subDirs = root.GetDirectories(); foreach (System.IO.DirectoryInfo dirInfo in subDirs) { // Resursive call for each subdirectory. WalkDirectoryTree(dirInfo); } } } static void ChangeFiles(string currentDirName) { char[] buffer = new char[10000]; string[] files = System.IO.Directory.GetFiles(currentDirName, "*.sql"); // files.Dump(); foreach (string fileName in files) { string.Format("Working on {0}", fileName).Dump(); FileInfo file = new FileInfo(fileName); string renamedFile = file.FullName + ".orig"; File.Move(file.FullName, renamedFile); using (StreamReader sr = new StreamReader(renamedFile)) using (StreamWriter sw = new StreamWriter(file.FullName, false)) { sw.Write("<Query Kind=\"SQL\">\r\n</Query >\r\n "); int read; while ((read = sr.Read(buffer, 0, buffer.Length)) > 0) sw.Write(buffer, 0, read); } File.Move(file.FullName, file.FullName + ".linq"); File.Delete(renamedFile); } } }