Find Text or Name of Stored Procedure in SQL Server

SQL Developer needs to find all stored procedures that have a specified text in the procedure name.
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.

The following stored procedure will list all stored procedure names whose text contains the parameter search string.

CREATE PROCEDURE Find_Text_In_SP
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
GO

The following stored procedure list all stored procedure names whose text contains the parameter search string.

CREATE PROCEDURE Find_SPName_With_Text
@StringToSearch varchar(100)
AS
SET @StringToSearch = '%' + @StringToSearch + '%'
SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name
GO

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.

SQL Server - Generation of Running Total

-- SQL Server running total quick syntax
SELECT SalesOrderID,
OrderDate = CONVERT(date, OrderDate),
O.TotalDue,
(SELECT sum(TotalDue)
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE SalesOrderID <= O.SalesOrderID) 'Running Total' FROM AdventureWorks2008.Sales.SalesOrderHeader O
GO
/* Partial results
SalesOrderID OrderDate TotalDue Running Total
43659 2001-07-01 27231.5495 27231.5495
43660 2001-07-01 1716.1794 28947.7289
43661 2001-07-01 43561.4424 72509.1713
43662 2001-07-01 38331.9613 110841.1326
43663 2001-07-01 556.2026 111397.3352
*/
-- SQL running totals - calculating running totals in sql - t sql running total
-- SQL row number over order by
USE AdventureWorks;
GO
DECLARE @Year INT, @Month INT
SET @Year = 2001
SET @Month = 7
SELECT SequenceNo = ROW_NUMBER()
OVER(ORDER BY OrderDate, SalesOrderID),
OrderDate = convert(CHAR(10),OrderDate,111),
SalesOrderId,
-- SQL dollar formatting - money / currency format
TotalDue = '$' + convert(VARCHAR,TotalDue,1),
[Running Total] = '$' + convert(VARCHAR,
(SELECT sum(TotalDue)
FROM Sales.SalesOrderHeader
WHERE SalesOrderID <= soh.SalesOrderID AND year(OrderDate) = @Year
AND month(OrderDate) = @Month), 1)
FROM Sales.SalesOrderHeader soh
WHERE year(OrderDate) = @Year
AND month(OrderDate) = @Month
ORDER BY SequenceNo;
GO
/* Partial results

SequenceNo OrderDate SalesOrderId TotalDue Running Total
1 2001/07/01 43659 $27,231.55 $27,231.55
2 2001/07/01 43660 $1,716.18 $28,947.73
3 2001/07/01 43661 $43,561.44 $72,509.17
4 2001/07/01 43662 $38,331.96 $110,841.13
5 2001/07/01 43663 $556.20 $111,397.34
6 2001/07/01 43664 $32,390.20 $143,787.54
7 2001/07/01 43665 $19,005.21 $162,792.75
*/
------------

-- SQL running total
-- SQL Server running total column
-- SQL correlated subquery
USE Northwind;
GO
SELECT o1.OrderID,
o1.ShipName,
o1.ShipCity,
OrderDate = convert(CHAR(10),OrderDate,112),
o1.Freight,
[FreightRunningTotal] =
(SELECT '$' + convert(VARCHAR,sum(o2.Freight),1)
FROM Orders o2
WHERE o2.OrderID <= o1.OrderID AND o2.ShipCountry = 'USA')
FROM Orders o1
WHERE o1.ShipCountry = 'USA'
ORDER BY o1.OrderID
GO

/* Partial results:
OrderID ShipName ShipCity OrderDate Freight FreightRunningTotal
10432 Split Rail Beer & Ale Lander 19970131 4.34 $1,989.40
10440 Save-a-lot Markets Boise 19970210 86.53 $2,075.93
10441 Old World Delicatessen Anchorage 19970210 73.02 $2,148.95
10452 Save-a-lot Markets Boise 19970220 140.26 $2,289.21
10469 White Clover Markets Seattle 19970310 60.18 $2,349.39
*/

------------

SQL SERVER – ReIndexing Database Tables and Update Statistics.

When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur.

In SQL Server it is very much important to reorganization your database.
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.

If you do reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.

--Script to automatically reindex all tables in a database
USE YourDatabaseName
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
EXEC sp_updatestats
GO