Ever had the need to find out where data is stored in your MS SQL Database?
I sometimes do and I find it often faster to simply search every table and every column within these table than to try and hunt down the schema definitions. I get a coffee while the script crunches away. The @SearchStrColumnName option is there mostly for when you search integers, as you may get too many false positives.
Edit: GitHub Gist works awesome, will have to do that for the previous posts too! Just paste the URL, and wordpress will do the rest, in this case display a fully highlighted T-SQL code.
Edit 4/8/2013 – @SearchStrColumnName wasn’t properly escaped before and this parameter didn’t really work.
Edit 4/9/2013 – Added parameter @FullRowResult, this will cause it to return the full row for each hit. This is usefull when you need to lookup or find related info to the search term.
Edit 4/12/2013 – Added parameter @SearchStrTableName, to limit in what tables we are going to search.
Edit 5/2/2013 – Now also searching in type uniqueidentifier (GUID)
Just a small hint… if you want to search all Views instead, this is easily possible with this.
Just change:
TABLE_TYPE = ‘BASE TABLE’
into
TABLE_TYPE = ‘VIEW’
And BAMMMM!
Comment by fullparam — April 23, 2013 @ 3:52 pm