Search results matching tags 'Administration' and 'DMVs'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Administration,DMVs&orTags=0Search results matching tags 'Administration' and 'DMVs'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Finding stored procedures containing %string%http://sqlblog.com/blogs/aaron_bertrand/archive/2009/11/09/finding-stored-procedures-containing-string.aspxMon, 09 Nov 2009 18:30:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:18684AaronBertrand
<p>Having just returned from PASS, I needed to play catch up on a few things in a stored procedure that was being developed before I left.&nbsp; Being a little frazzled still, I could not for the life of me remember the name of the stored procedure where those things existed.&nbsp; I constantly find myself manually writing queries like this to find all references to a column, table, variable or concept:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="black">[name] <br> </font><font color="blue">FROM </font><font color="green">sys.procedures <br></font><font color="blue"> WHERE </font><font color="magenta">OBJECT_DEFINITION</font><font color="gray">(</font><font color="magenta">OBJECT_ID</font><font color="gray">) LIKE </font><font color="red">'%string%'</font><font color="gray">; </font></pre>
</td>
</tr>
</table>
</blockquote>
<blockquote>
<p>or</p>
</blockquote>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="magenta">OBJECT_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">)<br></font><font color="blue"> FROM </font><font color="green">sys.sql_modules </font><font color="blue"><br> WHERE </font><font color="black">[definition] </font><font color="gray">LIKE </font><font color="red">'%string%'</font><font color="gray">; <br></font></pre>
</td>
</tr>
</table>
</blockquote>
<p>Yes, I could rely on dependencies *sometimes* - but even when they are not broken, I am not always trying to find things that show up in a dependency chain.</p>
<p>Now the queries above are a little tedious to write, but not too bad.&nbsp; But there are other complications that can often add a little bit of effort: <br></p>
<ul>
<li>When dealing with multiple owners/schema this can be more complex, so I would always add:</li>
</ul>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="magenta">OBJECT_SCHEMA_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">) <br> + </font><font color="red">'.' </font><font color="gray">+ </font><font color="black">[name] ...<br></font></pre>
</td>
</tr>
</table>
</blockquote>
<ul>
<li>And in the case of poorly chosen identifiers (hey, this isn't always against my own systems!), I would add QUOTENAME:</li>
</ul>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_SCHEMA_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">) <br> + </font><font color="red">'.' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="black">[name]</font><font color="gray">) </font><font color="black">...</font></pre></td>
</tr>
</table>
</blockquote>
<ul>
<li>And of course I would want the results to allow me to quickly get at the definition for those stored procedures, so I would want sp_helptext as part of the results:</li>
</ul>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">SELECT </font><font color="red">'EXEC sp_helptext ''' </font><font color="gray"><br> + </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_SCHEMA_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">) <br> + </font><font color="red">'.' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="black">[name]</font><font color="gray">) + </font><font color="red">'''' </font><font color="black">...<br></font></pre>
</td>
</tr>
</table>
</blockquote>
<p>This all gets very tedious to type, so a while back I decided to implement a set of stock, utility procedures in all of my databases (and don't tell my clients, but I've added it to some of their systems, too).&nbsp; I wanted one for procedures specifically, and then one that will search sql_modules so that it picks up functions and triggers as well. <br></p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Find_InSP<br>&nbsp;&nbsp; </font><font color="#434343">@string </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">)<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp; SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp; </font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cmd </font><font color="blue">= </font><font color="red">N'EXEC sp_helptext ''' <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_SCHEMA_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + </font><font color="red">'.' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="black">[name]</font><font color="gray">) + </font><font color="red">''';'<br>&nbsp;&nbsp; </font><font color="blue">FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="green">sys.procedures<br>&nbsp;&nbsp; </font><font color="blue">WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="magenta">OBJECT_DEFINITION</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">) LIKE </font><font color="red">N'%' </font><font color="gray">+ </font><font color="#434343">@string </font><font color="gray">+ </font><font color="red">'%'<br>&nbsp;&nbsp; </font><font color="blue">ORDER BY<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cmd</font><font color="gray">;<br></font><font color="blue">END<br></font><font color="black">GO<br>&nbsp;&nbsp; <br></font><font color="blue">CREATE PROCEDURE </font><font color="black">dbo.Find_InModule<br>&nbsp;&nbsp; </font><font color="#434343">@string </font><font color="blue">NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">)<br></font><font color="blue">AS<br>BEGIN<br>&nbsp;&nbsp; SET NOCOUNT ON</font><font color="gray">;<br><br>&nbsp;&nbsp; </font><font color="blue">SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cmd </font><font color="blue">= </font><font color="red">N'EXEC sp_helptext ''' <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_SCHEMA_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">))<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + </font><font color="red">'.' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_NAME</font><font color="gray">(</font><font color="black">[object_id]</font><font color="gray">)) + </font><font color="red">''';'<br>&nbsp;&nbsp; </font><font color="blue">FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="green">sys.sql_modules<br>&nbsp;&nbsp; </font><font color="blue">WHERE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">[definition] </font><font color="gray">LIKE </font><font color="red">N'%' </font><font color="gray">+ </font><font color="#434343">@string </font><font color="gray">+ </font><font color="red">'%'<br>&nbsp;&nbsp; </font><font color="blue">ORDER BY<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font><font color="black">cmd</font><font color="gray">;<br></font><font color="blue">END<br></font><font color="black">GO</font></pre>
</td>
</tr>
</table>
</blockquote>
<p>Now when I run:
</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">EXEC </font><font color="black">msdb.dbo.Find_InSP </font><font color="red">'agent'</font><font color="gray">;</font></pre>
</td>
</tr>
</table>
</blockquote>
<p>I get the following results (and as an added bonus, I can use CTRL in the grid results to copy the sp_helptext commands only for the procedures I am interested in):
</p>
<p style="margin-left:40px;"><img src="http://sqlblog.com/files/folders/18683/download.aspx" border="1" width="483" height="284"></p>
<p>I have also added the procedures to the model database, so that they exist in all future databases that are created as well.&nbsp; Does this save me a ton of time on any given day?&nbsp; Of course not.&nbsp; But every shortcut helps, right?&nbsp; I hope these procedures are useful for you.<br></p>A Gift of Script for 2008: Who's Active, What Are They Doing, and Who is Blocked?http://sqlblog.com/blogs/adam_machanic/archive/2007/12/31/a-gift-of-script-for-2008-who-s-active-what-are-they-doing-and-who-is-blocked.aspxMon, 31 Dec 2007 22:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:4300Adam Machanic<p>As with all of the blog posts I keep <i>meaning</i> to write -- I keep a list and given the infrequency with which I've been posting lately, it's getting quite large -- this script has been on the queue for quite some time. So here I find myself with a spare moment right on the cusp of the new year, and figured what better way to end the year than with a script that, at least for me, has been quite useful these last few months.</p><p>The driving force behind my writing this script is that I found myself endlessly calling <b>sp_who2 'active'</b> to see who was doing what on servers I needed to take a look at. Then I would have to sort through the results, and end up calling <b>DBCC INPUTBUFFER</b> to take a look at the SQL being used. This was a serious pain, and I finally caved a few months back and decided to end the madness once and for all with the help of some DMVs.</p><p>The following script primarily uses the <b>sys.dm_exec_requests</b> view, and finds all "active" requests -- i.e., those that are running, about to start running, or suspended. It also finds some other useful information, including the host name, login name, the start time of the batch, and whether or not the batch is currently blocked. In the outer query I use the <b>sys.dm_exec_sql_text</b> function to get the text of the SQL that all of the active requests are running, in addition to the SQL being run by the blocking sessions, if applicable. This way I don't have to do two lookups to chase down what's blocking what.</p><p>You'll notice that I use <b>FOR XML PATH</b> in the subqueries that pull the SQL text. This gives us a nice little bonus: instead of copying the text out of the cell in SSMS and pasting it somewhere else, you can simply click on it -- and it maintains whatever formatting, including white space and carriage returns, that it originally had. This is much, much nicer than getting the batch on a single line. The only problem is that certain characters, such as greater-than and less-than, get "entitized" when the text is converted to XML. This means that some queries won't be able to be run without a bit of editing. A small price to pay for nicer output, in my opinion. If anyone out there has a solution for the entitization, please let me know! The only way I know to solve it is to convert back to VARCHAR, and that defeats the whole purpose...</p><p>Anyway, thanks all for a great 2007. Here's to an even better 2008! Without further ado, the script:</p><blockquote>&nbsp;</blockquote><blockquote>SELECT<br>&nbsp;&nbsp;&nbsp; x.session_id,<br>&nbsp;&nbsp;&nbsp; x.host_name,<br>&nbsp;&nbsp;&nbsp; x.login_name,<br>&nbsp;&nbsp;&nbsp; x.start_time,<br>&nbsp;&nbsp;&nbsp; x.totalReads,<br>&nbsp;&nbsp;&nbsp; x.totalWrites,<br>&nbsp;&nbsp;&nbsp; x.totalCPU,<br>&nbsp;&nbsp;&nbsp; x.writes_in_tempdb,<br>&nbsp;&nbsp;&nbsp; (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; text AS [text()]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM sys.dm_exec_sql_text(x.sql_handle)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR XML PATH(''), TYPE<br>&nbsp;&nbsp;&nbsp; ) AS sql_text,<br>&nbsp;&nbsp;&nbsp; COALESCE(x.blocking_session_id, 0) AS blocking_session_id,<br>&nbsp;&nbsp;&nbsp; (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p.text<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIN(sql_handle) AS sql_handle<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM sys.dm_exec_requests r2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r2.session_id = x.blocking_session_id<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) AS r_blocking<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CROSS APPLY<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; text AS [text()]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM sys.dm_exec_sql_text(r_blocking.sql_handle)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FOR XML PATH(''), TYPE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) p (text)<br>&nbsp;&nbsp;&nbsp; ) AS blocking_text<br>FROM<br>(<br>&nbsp;&nbsp;&nbsp; SELECT<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.session_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.host_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.login_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.start_time,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.sql_handle,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.blocking_session_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(r.reads) AS totalReads,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(r.writes) AS totalWrites,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(r.cpu_time) AS totalCPU,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb<br>&nbsp;&nbsp;&nbsp; FROM sys.dm_exec_requests r<br>&nbsp;&nbsp;&nbsp; JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id<br>&nbsp;&nbsp;&nbsp; JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id<br>&nbsp;&nbsp;&nbsp; WHERE r.status IN ('running', 'runnable', 'suspended')<br>&nbsp;&nbsp;&nbsp; GROUP BY<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.session_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.host_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s.login_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.start_time,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.sql_handle,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; r.blocking_session_id<br>) x<br></blockquote><p>&nbsp;</p><p>Enjoy!<br></p>