Monday, July 14, 2003

Search all objects in all DBs for code fragments By: cadebryant - Posted: 6/10/2003 6:48:22 PM  (Stored Procedure) This procedure allows you to search through all objects in all databases on your server for words/phrases in your object code.  Very handy for cases in which, for example, a column name on a table has been changed and you need to search your entire server for any sprocs/views/etc. that might reference it. Syntax: [EXEC] sp_FindCodeStr [@SearchStr=]'', [@PrintOnly=]{0, 1} Notice that no cursors or temp tables are used in this sproc.  Dynamic T-SQL execution appends all the needed statements onto the @sql variable, building the batch in a set-oriented manner, and then the final batch is executed via sp_executesql.  As a result, this sproc performs surprisingly well. Today's procedure is probably one of my favorites. Xp_fixeddrives tells you all the hard drives that are on your SQL Server and how much space you have on each of them. This is a great monitoring script for determining low hard drive space and I use it to raise alerts typically. Another useful application would be to execute it before firing off an extract process to ensure you have space for the ETL before running the load procedure. To execute xp_fixeddrives, use the following syntax: master..xp_fixeddrives which will output results similar to this: drive MB free ----- ----------- C 1773 D 1386 E 12429 F 29276   9:06:56 AM    trackback []     Articulate []