Search Instance for a Stored Procedure usage

References:
Recently, I had the need to skim acrossed all the databases on a single instance.  In doing so I figured that the infamous 'sp_msforeachdb' undocumented stored procedure would be useful but was unsure on the methodology or pattern to implement.



So i started first with the ability to simply find the specific SProc within a known database.  This got me started with using various of the practices described in Greg Robidoux's article, with "Method 3 - Using syscomments" being the winner for my needs.

So then i migrated to the pattern i need for skimming all the user databases for the implementation of the SProc, which lead me to work on the implementation using "sp_msforeachdb".  By the end this is the final resulting pattern, with redaction of course.


1:  exec sp_msforeachdb '  
2:  IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN  
3:       declare @tbl Table (db sysname, sproc varchar(255));  
4:    
5:       insert into @tbl (  
6:            db, sproc  
7:       ) SELECT distinct   
8:            ''[?]'', ss.name + ''.'' + so.name  
9:       from [?].sys.objects so  
10:            inner join [?].sys.schemas ss  
11:                      on so.schema_id = ss.schema_id  
12:            inner join [?].sys.syscomments sc  
13:                      on so.object_id = sc.id  
14:       WHERE charindex(''SPROC_NAME'', text) > 0  
15:    
16:       select * from @tbl  
17:  END  
18:  '

After some further usage, needed to do a further simplification on the "Results" tab.  For my situation it spammed over 50 databases, which in most cases the SProc i was searching for was consistently located on one database (source) but possibly on 1 or 2 other databases.  So i had to update the ultimately pattern to check for value.  Some may prefer the Exists methodology but in this situation i used count.


1:  exec sp_msforeachdb '  
2:  IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN  
3:       declare @tbl Table (db sysname, [schema] varchar(255), sproc varchar(255));  
4:    
5:       insert into @tbl (  
6:            db, [schema], sproc  
7:       ) SELECT distinct   
8:            ''[?]'', ss.name, so.name  
9:       from [?].sys.objects so  
10:            inner join [?].sys.schemas ss  
11:                      on so.schema_id = ss.schema_id  
12:            inner join [?].sys.syscomments sc  
13:                      on so.object_id = sc.id  
14:       WHERE charindex(''SPROC_NAME'', text) > 0  
15:    
16:       if (select count(*) from @tbl) > 0 begin  
17:            select db, [schema] + ''.'' + sproc  
18:            from @tbl  
19:            order by [schema], sproc  
20:    
21:       end  
22:  END  
23:  '  
24:

Comments

Post a Comment

Popular posts from this blog

SysInternals - BgInfo for ALL Users

Image
I have seen various sites that have been pretty good at getting close to the point of getting Background Information created by the tool BgInfo but none of them have actually solved my specific problem. Solutions currently out there include: Placing into the "All Users" folder This did not work for me, as our implementation does not contain an "All Users" folder in the "Users" folder for profiles. Reg-Hacking and HKCU or HKLM entry This is the worst of all the bad choices available.  I am experienced with Registry Editing and I still would not want or find a need to do this. Placing into the "Default" folder This is good going forward but what about those 20+ user profiles that are already logged in.  FYI, if your server has 20+ user account profiles in the "Users" directory you have larger problems than I am willing to assist with in this posting. The solution that worked for me, will be described below in pain-staki...
Read more

Linked Server....

Well today, I started the fun filled adventure of managing data from/to a LinkedServer. During this learning curve, I found plenty of information about how to query from and instert into a Linked server but most of them utilized the OpenQuery  TSQL Statement to operate. One failure of the concepts is to use the inline statement to access the Linked Server. In my situation, i was linking to a MySQL database offsite.  Below you will find the MSSQL statement patterns to used when utilizing query statements against the Linked Server. Select: 1: Select {cols} 2: From {LinkedServer}...{table} 3: [Conditions] Insert: 1: Insert Into {LinkedServer}...{table} [cols,...] 2: Values ([cols,...]) There are more commands you can use but the point is that the developer/dba is utilizing only the 'server' and 'table' portions of the source information.  This being, as described by Microsoft, as ' {server}.{database}.{schema}.{table} ' is simply ' ...
Read more

JSON/AJAX Helpers

Recently, had an implementation where I needed to implement ASP.Net Web API 2 and accompanied it with Javascript/JQuery AJAX communication.  In doing this i found myself making the same pattern calls with some varying value for certain properties for the AJAX data calls.  As such, i ended up building a few helper functions to simply the inclusion of these various minor quirks based on what was needed.  Below  you will find the various forms. 1: /********************************************************************* 2: @type - dataType, such as 'json', 'xml', 'script', 'jsonp' or 'html' 3: @contentType - string value, represents the HTML type of content 4: IE: 'application/json; charset=utf-8' 5: @url - API URL, such as 'api/tasks' should be constrainted to the 6: Web API Controller 7: @success - Function(data, status, xhr) handler, The logic to execute 8: on a 200 res...
Read more