SQL Hide ‘n Seek

Categories: Corner, News, Professional, SSC
Comments: 1 Comment
Published on: July 11, 2012

When was the last time you had to find something within your database?  Did it feel more like a child’s game or an arduous task?

Child's Game or Arduous Task

What would you say if it could be more like child’s play (like the baby elephant is portraying) than a chore?

Child’s Play

The simplest solution may be to use a tool from a respectable vendor.  One such tool that comes to mind is SQL Search from RedGate.  You can find out more about that tool here.

SQL Search does require that an application be installed on the machine on which you will be using the search feature.  The tool is fine and I will leave it up to you to use or not use it.

Alternative

Instead of installing an application onto your machine, you could always write your own script.  The information is readily available within SQL Server for you to find the objects you seek.

For example, if I wanted to find any tables that had a particular column, I could use the following.

[codesyntax lang="tsql"]

[/codesyntax]

Granted, this query returns a bit more information than you require.  Personally, I like to see the additional information related to the columns as I am doing a search through a database.  I always find it interesting to find columns of the same name and intent but to have a different definition within the database.

And if you desire to find code within the database that contains a particular column name, then something like the following could be helpful.

[codesyntax lang="tsql"]

[/codesyntax]

Now, what if I want to search code and tables at the same time for a particular column name usage?  Well, I could take advantage of the following.

[codesyntax lang="tsql"]

[/codesyntax]

Conclusion

Now, I have a script that will return a row for each time a column appears in a proc.  I can correlate which table and column matches to the proc and get the results I need quickly.

Now, you can take this and have a little fun with it.

1 Comment - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
July 2012
M T W T F S S
« Jun   Sep »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp

  • @GEEQL: #sqlhelp question from daughter: "why is it 'more optimized' to list 6 tables in a single from statement than it is to do JOINs?"
  • @BrentO: @markmurphynyc This is kinda beyond 140 - best to put the full queries & plans on a Q&A web site. #sqlhelp
  • @markmurphynyc: #SQLHELP But if I filter by S_DATE_ID in the D_DATE dim, it does use partition elimination. This is in 2012.
  • @markmurphynyc: #SQLHELP Queries filtering by calendar date in d_date joined to the fact table by s_date_id aren't using partition elim. Any way to force?
  • @markmurphynyc: #SQLHELP (1/2) Fact table has s_date_id as YYYYMMDD and is partitioned monthly by this key. D_Date has same numeric key, plus calendar date
  • @jlangdon: @SQLSoldier Also, when hyperthreading is turned on each thread is licensed which sucks. Not applicable with SA @YetAnotherSQL #sqlhelp
  • @jlangdon: @SQLSoldier Correct. Question asked was about EE which is what we have too.@YetAnotherSQL #sqlhelp
  • @databasedave: Any additional info on wait types "se repl commit ack" or "resource governor idle"? Cant find anything on the interwebs. #sqlhelp #azure
  • @SQLHammer: #sqlhelp why does my SSMS open and recovery all of my query windows after I close it explicitly? It's like it thought it crashed.
  • @SQLSoldier: @jlangdon @YetAnotherSQL Only if you oversubscribe the CPUs which I recommend never doing, and need to use Ent. Ed. #sqlhelp

Welcome , today is Tuesday, September 16, 2014