tag:blogger.com,1999:blog-42923305168836851972024-02-20T10:07:33.552-08:00InformationHemalhttp://www.blogger.com/profile/00623457607619389745noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4292330516883685197.post-39173239233738105282010-05-03T04:23:00.001-07:002010-05-03T04:36:11.830-07:00Find Text or Name of Stored Procedure in SQL Server<span style="font-family:verdana;font-size:85%;"></span><br /><span style="font-family:verdana;font-size:85%;">SQL Developer needs to find all stored procedures that have a specified text in the procedure name.<br />Sometime we needed to find procedure names that have a certain text in their names. This article will give you the SQL statements for doing just that.<br /><br />The following stored procedure will list all stored procedure names whose text contains the parameter search string.<br /><br /><span style="color:#3333ff;">CREATE PROCEDURE</span> Find_Text_In_SP<br />@StringToSearch <span style="color:#3333ff;">varchar</span>(100)<br /><span style="color:#3333ff;">AS</span><br /><span style="color:#3333ff;">SET</span> @StringToSearch = '%' +@StringToSearch + '%'<br /><span style="color:#3333ff;">SELECT</span> Distinct SO.Name<br /><span style="color:#3333ff;">FROM</span> sysobjects SO (<span style="color:#3333ff;">NOLOCK</span>)<br /><span style="color:#3333ff;">INNER JOIN</span> syscomments SC (<span style="color:#3333ff;">NOLOCK</span>) on SO.Id = SC.ID<br /><span style="color:#3333ff;">AND</span> SO.Type = 'P'<br /><span style="color:#3333ff;">AND</span> SC.Text LIKE @stringtosearch<br /><span style="color:#3333ff;">ORDER BY</span> SO.Name<br /><span style="color:#3333ff;">GO</span><br /><br />The following stored procedure list all stored procedure names whose text contains the parameter search string.<br /><br /><span style="color:#3333ff;">CREATE PROCEDURE</span> Find_SPName_With_Text<br />@StringToSearch <span style="color:#3333ff;">varchar</span>(100)<br /><span style="color:#3333ff;">AS</span><br /><span style="color:#3333ff;">SET</span> @StringToSearch = '%' + @StringToSearch + '%'<br /><span style="color:#3333ff;">SELECT</span> DISTINCT SO.NAME<br /><span style="color:#3333ff;">FROM</span> SYSOBJECTS SO (NOLOCK)<br /><span style="color:#3333ff;">WHERE</span> SO.TYPE = 'P'<br /><span style="color:#3333ff;">AND</span> SO.NAME LIKE @StringToSearch<br /><span style="color:#3333ff;">ORDER BY</span> SO.Name<br /><span style="color:#3333ff;">GO</span> </span><br /><span style="font-family:verdana;font-size:85%;"><br />You can also use the code of the stored procedures in Query Analyzer instead of making a stored proc out of the code shown above.</span>Hemalhttp://www.blogger.com/profile/00623457607619389745noreply@blogger.com0tag:blogger.com,1999:blog-4292330516883685197.post-41436937311827975792010-05-03T03:48:00.000-07:002010-05-03T04:36:11.833-07:00SQL Server - Generation of Running Total<span style="font-family:verdana;font-size:85%;"><br /><span style="color:#006600;">-- SQL Server running total quick syntax<br /></span><span style="color:#3333ff;">SELECT</span> SalesOrderID,<br />OrderDate = <span style="color:#cc33cc;">CONVERT</span>(date, OrderDate),<br />O.TotalDue,<br />(<span style="color:#3333ff;">SELECT</span> <span style="color:#cc33cc;">sum</span>(TotalDue)<br /><span style="color:#3333ff;">FROM</span> AdventureWorks2008.Sales.SalesOrderHeader<br /><span style="color:#3333ff;">WHERE</span> SalesOrderID <= O.SalesOrderID) 'Running Total' <span style="color:#3333ff;">FROM</span> AdventureWorks2008.Sales.SalesOrderHeader O<br /><span style="color:#3333ff;">GO</span><br /><span style="color:#006600;">/* Partial results<br />SalesOrderID OrderDate TotalDue Running Total<br />43659 2001-07-01 27231.5495 27231.5495<br />43660 2001-07-01 1716.1794 28947.7289<br />43661 2001-07-01 43561.4424 72509.1713<br />43662 2001-07-01 38331.9613 110841.1326<br />43663 2001-07-01 556.2026 111397.3352<br />*/ </span><br /><span style="color:#006600;">-- SQL running totals - calculating running totals in sql - t sql running total<br />-- SQL row number over order by<br /></span><span style="color:#3333ff;">USE</span> AdventureWorks;<br /><span style="color:#3333ff;">GO</span><br /><span style="color:#3333ff;">DECLARE</span> @Year <span style="color:#3333ff;">INT</span>, @Month <span style="color:#3333ff;">INT<br /></span><span style="color:#3333ff;">SET</span> @Year = 2001<br /><span style="color:#3333ff;">SET</span> @Month = 7<br /><span style="color:#3333ff;">SELECT</span> SequenceNo = ROW_NUMBER()<br /><span style="color:#3333ff;">OVER</span>(<span style="color:#3333ff;">ORDER BY</span> OrderDate, SalesOrderID),<br />OrderDate = <span style="color:#cc33cc;">convert</span>(<span style="color:#3333ff;">CHAR</span>(10),OrderDate,111),<br />SalesOrderId,<br /><span style="color:#006600;">-- SQL dollar formatting - money / currency format </span><br />TotalDue = '$' + <span style="color:#cc33cc;">convert</span>(<span style="color:#3333ff;">VARCHAR</span>,TotalDue,1),<br />[Running Total] = '$' + <span style="color:#cc33cc;">convert</span>(<span style="color:#3333ff;">VARCHAR</span>,</span><br /><span style="font-family:verdana;font-size:85%;">(<span style="color:#3333ff;">SELECT</span> <span style="color:#cc33cc;">sum</span>(TotalDue)<br /><span style="color:#3333ff;">FROM</span> Sales.SalesOrderHeader<br /><span style="color:#3333ff;">WHERE</span> SalesOrderID <= soh.SalesOrderID <span style="color:#3333ff;">AND</span> year(OrderDate) = @Year<br /><span style="color:#3333ff;">AND</span> month(OrderDate) = @Month), 1)<br /><span style="color:#3333ff;">FROM</span> Sales.SalesOrderHeader soh<br /><span style="color:#3333ff;">WHERE</span> year(OrderDate) = @Year<br /><span style="color:#3333ff;">AND</span> month(OrderDate) = @Month<br /><span style="color:#3333ff;">ORDER BY</span> SequenceNo;<br /><span style="color:#3333ff;">GO</span><br /><span style="color:#006600;">/* Partial results<br /><br />SequenceNo OrderDate SalesOrderId TotalDue Running Total<br />1 2001/07/01 43659 $27,231.55 $27,231.55<br />2 2001/07/01 43660 $1,716.18 $28,947.73<br />3 2001/07/01 43661 $43,561.44 $72,509.17<br />4 2001/07/01 43662 $38,331.96 $110,841.13<br />5 2001/07/01 43663 $556.20 $111,397.34<br />6 2001/07/01 43664 $32,390.20 $143,787.54<br />7 2001/07/01 43665 $19,005.21 $162,792.75<br />*/<br />------------<br /></span><br /><span style="color:#006600;">-- SQL running total<br />-- SQL Server running total column<br />-- SQL correlated subquery</span><br /><span style="color:#3333ff;">USE</span> Northwind;<br />GO<br /><span style="color:#3333ff;">SELECT</span> o1.OrderID,<br />o1.ShipName,<br />o1.ShipCity,<br />OrderDate = <span style="color:#cc33cc;">convert</span>(<span style="color:#3333ff;">CHAR</span>(10),OrderDate,112),<br />o1.Freight,<br />[FreightRunningTotal] =<br />(<span style="color:#3333ff;">SELECT</span> '$' + <span style="color:#cc33cc;">convert</span>(<span style="color:#3333ff;">VARCHAR</span>,sum(o2.Freight),1)<br /><span style="color:#3333ff;">FROM</span> Orders o2<br /><span style="color:#3333ff;">WHERE</span> o2.OrderID <= o1.OrderID <span style="color:#3333ff;">AND</span> o2.ShipCountry = 'USA') </span><br /><span style="font-family:verdana;font-size:85%;"><span style="color:#3333ff;">FROM</span> Orders o1 </span><br /><span style="font-family:verdana;font-size:85%;"><span style="color:#3333ff;">WHERE</span> o1.ShipCountry = '<span style="color:#cc0000;">USA</span>' </span><br /><span style="font-family:verdana;font-size:85%;"><span style="color:#3333ff;">ORDER BY</span> o1.OrderID<br />GO<br /><br /><span style="color:#006600;">/* Partial results:<br />OrderID ShipName ShipCity OrderDate Freight FreightRunningTotal<br />10432 Split Rail Beer & Ale Lander 19970131 4.34 $1,989.40<br />10440 Save-a-lot Markets Boise 19970210 86.53 $2,075.93<br />10441 Old World Delicatessen Anchorage 19970210 73.02 $2,148.95<br />10452 Save-a-lot Markets Boise 19970220 140.26 $2,289.21<br />10469 White Clover Markets Seattle 19970310 60.18 $2,349.39<br />*/<br /></span><br />------------ </span>Hemalhttp://www.blogger.com/profile/00623457607619389745noreply@blogger.com0tag:blogger.com,1999:blog-4292330516883685197.post-64033479396129791502010-05-03T02:55:00.000-07:002010-05-03T04:36:11.838-07:00SQL SERVER – ReIndexing Database Tables and Update Statistics.<span style="font-family:verdana;"><span style="font-size:85%;"><span style="color:#000000;"></span></span></span><br /><span style="font-family:verdana;"><span style="font-size:85%;"><span style="color:#000000;">When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur.<br /></span></span></span><br /><span style="font-family:verdana;"><span style="font-size:85%;"><span style="color:#000000;">In SQL Server it is very much important to reorganization your database.<br />Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.<br /></span></span></span><br /><span style="font-family:verdana;"><span style="font-size:85%;"><span style="color:#000000;">If you do reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.<br /></span><br /></span><span style="font-size:85%;"><span style="color:#000000;">--Script to automatically reindex all tables in a database<br /></span><span style="color:#3333ff;">USE</span> YourDatabaseName<br />GO<br /><span style="color:#3333ff;">EXEC</span> <span style="color:#993300;">sp_MSforeachtable</span> @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"<br />GO<br /><span style="color:#3333ff;">EXEC</span> <span style="color:#660000;">sp_updatestats</span><br />GO</span><span style="font-size:78%;"> </span></span>Hemalhttp://www.blogger.com/profile/00623457607619389745noreply@blogger.com0