SQL – Name That Stored Procedure!
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?
SQL Intellisense Refresh
This one is pretty tech specific. Ignore if you don't know or care what SQL, SSMS, or Intellisense are.
When working with Microsoft SQL Server Management Studio, you will often find yourself making changes to table structures in one window and writing queries in another. Very useful ability but schema changes are not automatically reflected by Intellisense in all open query windows.
Quick example:
You create table myTable with 2 columns:
CREATE TABLE [dbo].[myTable](
[Zone] [int] NULL,
[Item] [nchar](6) NULL
) ON [PRIMARY]GO
And then in another window you have this simple select:
select zone, item from myTable
Then you decide to add a new column to myTable, [iName] nchar(8) NULL
Not a big deal in this context. YOU know the column is there. But in more complex queries Intellisense will do all sorts of nasty things to you as you bang out your queries. Like autocompleting your table names or columns with crazy replacements.
How to fix this without closing the query window and opening a new one? Easy!
CTRL + SHIFT + R
OR
Edit -> Intellisense -> Refresh
Handy shortcut I thought. Thanks to @scottstonehouse for bringing this problem to mind.
SQL Send Email 4000 Char Limit
Use SQL to send emails with query results? Great for reporting purposes and useful with dynamic queries.
I won't go into all the little details, you can Google them as easily as I can. Well, ok, I'll make it easy, they are here.
The one issue I've had with sp_send_dbmail is the character limits for the @Query parameter. I can hear you saying, why are you passing in a query with more than 4000 characters? Well Dorothy, sometimes I need that many JOINs to make it work. But seriously, consider the poor person who comes behind you.
I'm an avid commenter in queries as well as code. Once I found myself removing all the line breaks, extra spaces, and fully qualified column names just to make my dynamic query fit into 4000 characters, I had to step back and re-think my approach.
Instead of passing in a normal select statement I decided to declare a new varchar variable and construct an EXEC statement. Ran a quick test and it worked!
Goodbye 4000 char limit. I can create sub-processes for any dynamic SQL I need.
Example:
Regular Query
Declare @SQL = 'Select top 1 * from myTable'
exec msdb..sp_send_dbmail @profile_name='sa', @recipients=N'jon@home.ca', @subject='TEST', @Query=@SQL
Updated to use Stored Procedure
Declare @SQL = 'EXEC mySproc ' + @myParam
exec msdb..sp_send_dbmail @profile_name='sa', @recipients=@recips, @subject=@Subj, @Query=@SQL, @execute_query_database = 'myDB',@Body = 'Hey I'm adding a header to the email!'
I added a couple of extra parameters to sp_send_dbmail just for fun. The most import one is @execute_query_database, you need that to run EXEC commands.
Any questions, drop me a comment or @jonreid on Twitter.
About Jon Reid
Western Star Columns
Categories
Tags
Recent Posts
- For the Love of -h!
- A Rose By Any Other Name, May Not Compile
- Space and Back Again – the Yo-Yo Effect in American Politics
- Corner Brook Library Sale 2011
- Google AdWords in NL Politics