Conversations On tech, literature, art and business

17Jun/111

SQL – Name That Stored Procedure!

the lost and found forest

Lost and Found Forest - Credit: http://www.shedoesthecity.com/the_women_of_nuit_blanche_rina_grosman

Opening SQL with the purpose of finding a specific query can be a daunting task. In any system that has been used for a few years detritus is plentiful.

Unused procedures, long dead tables, backups, and non-refactored name conventions can make finding anything an arduous task.

In the case of larger systems it can even be a case of the forest obscuring the trees (network humor).

So, how does one find a stored procedure about which little is known?

  • You can certainly try checking the dependencies of a table you suspect is involved.
  • You can scan through the myriad stored procedures in what you think is the correct database hoping the name will give you a clue.
  • OR, you can fast scan each database using the following queries:

Let's assume you know a table name or term that is used in the stored procedure you're looking for. Use this tidbit of information as your search criteria and try one of the following:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%searchterm%'

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%searchterm%'

These queries will return database object names that have the searched term in them. Simply run these queries against each database you have until you find something that looks right. Not a perfect solution but it has saved me some time in the past.

For example, using the msdb database and the search term '%validate_user%' I get the results:

Name
sp_send_dbmail
sp_validate_user

Any tricks you use to speed up this process?

Tagged as: 1 Comment
9Jun/110

Tumbleweeds – Why I Haven’t Posted in a Month

tumbleweed

Photo credit bbenedet https://www.pierce.ctc.edu

This isn't a whiny apologetic post about why I'm too busy with work, home life, or my furby collection (I don't have one fyi) to blog.

I have been busy but I've also been trying out Tumblr, the neato micro-blogging platform.

When I first saw Tumblr, I  thought why? I have a blog already. it's just something else to update and monitor, right?

Well, there are lots of things I'd like to share that are perhaps not best expressed in a blog post. Either I don't have to time to put it into a nice formal post, or I just don't think it fits with what I'm trying to do here.

Of course, if it's too little to blog, then tweet it, yeah? I do have Twitter, and I use it plenty but posting pictures, quotes, and other interesting tidbits there lack the impact that a Tumblr stream has when viewed in full.

When I find a new Tumblr user, I can quickly skim through their stream and get an idea of what they are into and whether I care. That can take a bit more time with a Twitter stream and biography.

I use Tumblr primarily as quick posting tool, a picture, or quote. As time goes on I use Twitter more interactively, less broadcast only. Twitter is great for mini-conversations and for tracking items of specific interest using hashtags like #econobrook.

Besides, I've been ignoring Facebook for a very long time. Now Tumblr and my blog are both wired to post to my FB account.

I'm seeing a pattern emerge where all these different media streams work together without overwhelming me in a constant need to check and update all of them all the time. I can use whichever outlet I find appropriate at the time and know that the message will find it's way out to all my online friends.

  • On the go? Tweet
  • See a cool pic online? Tumble
  • Have a comprehensive thought about something? Blog

A feature I like with Tumblr is their use of tags. For example, I've started a little picture story using my Scott Pilgrim desk buddy. So, while you will see Scott sprinkled through my Tumblr stream, if you want to see the Scott Pilgrim VS the Office story in full you can hit: http://tumbling.jonreid.ca/tagged/scottpilgrim.

I tried adding my Tumblr feed to my sidebar like I have with my Twitter stream but I found it cluttered things too much. Instead, I gave it a page of it's own. Yes, it's a flash widget. I'll convert to JQuery at some point but right now I'm trying out WidgetBox. Easy to hook up with my Tumblr RSS feed and it didn't take an all night coding session.

What do you think? Is Tumblr a waste of time? Is Twitter? Or are there other tools you prefer?

Tagged as: , No Comments
   

About Jon Reid

Coder, cook, columnist and family man whose aim is to help build the tech community in NL.
Linked In link 

RSS Western Star Articles

Categories

Tags

Apple art corner brook eats Economy gadgets Galaxy iPhone local Macworld Marketing Mobile moncton MS-SQL phishing productivity shortcuts Tools twitter Virtual Shopping weather web

Recent Posts

Archives