Search all of the text fields in a sql server database for a particular value... with LINQ (Pad)

Aug 30, 10

So, the question was posed on how to do this with LINQPad, and thus with LINQ. Here is one way. This is basically the same thing I did in my last post about searching for this kind of value. This also shows how to return an arbitrary result from a linq query to a very generic class object for later processing. Note that I was not able to use params because the table name couldn’t be a param. I’m sure there is probably some more elegant way to do this, but this works fine and hopefully this is one of those scripts/snippets of code that you never have to use. Since you can just do something like this with SQL, I’m not sure of the usefulness of this code in a realistic setting, but I suppose if you needed to throw up a utility page and you already had a LINQ data context in your app for other stuff, you could do something in a pinch using executequery. I’m personally not a big fan of using executequery as it stands today. I generally just take it as a sign I need to switch to sql, but for something like this (I mean, I could be joining on a file with a list of text to look for right?), the versatility is nice to have. 😀

	//meant to run in LINQPad
	void Main() {

	this.ExecuteCommand("set transaction isolation level read uncommitted");

	string FindThis = "some text to find";

	string q = @"
	select
	'[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' [t]
	, '[' + column_name + ']' [c]
	from
	INFORMATION_SCHEMA.COLUMNS
	where
	data_type in ('nvarchar','nchar','varchar','char')
	and character_maximum_length >= len({0})
	";

	string e = @"
	select
	case
	when exists(
	select *
	from {0} with (nolock)
	where {1} like '%{2}%'
	)
	then 1
	else 0
	end";

	var matches = from a in this.ExecuteQuery<tc>(q,FindThis).Where (t =>
	this.ExecuteQuery<int>(e.Replace("{0}",t.t).Replace("{1}",t.c).Replace("{2}",FindThis)
	).Single().Equals(1)
	) select a;

	matches.Dump();

	}

	class tc {
	public string t{get;set;}
	public string c{get;set;}
	}

view raw search-sql-text.cs hosted with ❤ by GitHub