Re-throwing exception generated by RAISERROR

 You can re-throw error that you catch in CATCH block using TRHOW statement:

DECLARE @msg nvarchar(50) = 'Here is a problem! Area: ''%s'' Line:''%i'''
BEGIN TRY
	print 'First statement';
	RAISERROR(@msg, 11, 1, 'TRY BLOCK', 2);
	print 'Second statement';
END TRY
BEGIN CATCH
	print 'Error: ' + ERROR_MESSAGE();
	THROW;
END CATCH

Output:

First statement

Error: Here is a problem! Area: 'TRY BLOCK' Line:'2'

Msg 50000, Level 11, State 1, Line 4

Here is a problem! Area: 'TRY BLOCK' Line:'2'

THROW is similar to RAISERROR with following differences:

• Recommendation is that new applications should use THROW instead of RASIERROR.
• THROW can use any number as first argument (error number), RAISERROR can use only ids in sys.messages view
• THROW has severity 16 (cannot be changed)
• THROW cannot format arguments like RAISERROR. Use FORMATMESSAGE function as an argument of RAISERROR if you need this feature.

If Else Overview in SQL

Single IF statement

Like most of the other programming languages, T-SQL also supports IF..ELSE statements.

For example in the example below 1 = 1 is the expression, which evaluates to True and the control enters the BEGIN..END block and the Print statement prints the string 'One is equal to One'

IF ( 1 = 1) --<-- Some Expression
BEGIN
    PRINT 'One is equal to One'
END

Multiple IF Statements:

We can use multiple IF statement to check multiple expressions totally independent from each other.

In the example below, each IF statement's expression is evaluated and if it is true the code inside the BEGIN...END block is executed. In this particular example, the First and Third expressions are true and only those print statements will be executed.

IF (1 = 1) --<-- Some Expression --<-- This is true
    BEGIN
	PRINT 'First IF is True' --<-- this will be executed
    END

IF (1 = 2) --<-- Some Expression
    BEGIN
	PRINT 'Second IF is True'
    END

IF (3 = 3) --<-- Some Expression --<-- This true
    BEGIN
	PRINT 'Third IF is True' --<-- this will be executed
    END

Single IF..ELSE statement:

In a single IF..ELSE statement, if the expression evaluates to True in the IF statement the control enters the first BEGIN..END block and only the code inside that block gets executed , Else block is simply ignored.

On the other hand if the expression evaluates to False the ELSE BEGIN..END block gets executed and the control never enters the first BEGIN..END Block.

In the Example below the expression will evaluate to false and the Else block will be executed printing the string 'First expression was not true'

IF ( 1 <> 1) --<-- Some Expression
	BEGIN
		PRINT 'One is equal to One'
	END
ELSE
	BEGIN
		PRINT 'First expression was not true'
	END

Multiple IF...ELSE Statements

More often than not we need to check multiple expressions and take specific actions based on those expressions. This situation is handled using multiple IF...ELSE IF statements.

In this example all the expressions are evaluated from top to bottom. As soon as an expression evaluates to true, the code inside that block is executed. If no expression is evaluated to true, nothing gets executed.

IF (1 = 1 + 1)
	BEGIN
		PRINT 'First If Condition'
	END
ELSE IF (1 = 2)
	BEGIN
		PRINT 'Second If Else Block'
	END
ELSE IF (1 = 3)
	BEGIN
		PRINT 'Third If Else Block'
	END
ELSE IF (1 = 1) --<-- This is True
	BEGIN
		PRINT 'Last Else Block' --<-- Only this statement will be printed
	END

IF... ELSE with final ELSE Statements

If we have Multiple IF...ELSE IF statements but we also want also want to execute some piece of code if none of expressions are evaluated to True , then we can simple add a final ELSE block which only gets executed if none of the IF or ELSE IF expressions are evaluated to true.

In the example below none of the IF or ELSE IF expression are True hence only ELSE block is executed and prints 'No other expression is true'

IF ( 1 = 1 + 1 )
	BEGIN
		PRINT 'First If Condition'
	END
ELSE IF (1 = 2)
	BEGIN
		PRINT 'Second If Else Block'
	END
ELSE IF (1 = 3)
	BEGIN
		PRINT 'Third If Else Block'
	END
ELSE
	BEGIN
		PRINT 'No other expression is true' --<-- Only this statement will be printed
	END

What are dirty reads in SQL Server

 Dirty reads (or uncommitted reads) are reads of rows which are being modified by an open transaction.

This behavior can be replicated by using 2 separate queries: one to open a transaction and write some data to a table without committing, the other to select the data to be written (but not yet committed) with this isolation level.

Query 1 - Prepare a transaction but do not finish it:

CREATE TABLE dbo.demo (
    col1 INT,
    col2 VARCHAR(255)
);
GO
--This row will get committed normally:
BEGIN TRANSACTION;
INSERT INTO dbo.demo(col1, col2)
    VALUES (99, 'Normal transaction');
COMMIT TRANSACTION;
--This row will be "stuck" in an open transaction, causing a dirty read
BEGIN TRANSACTION;
INSERT INTO dbo.demo(col1, col2)
    VALUES (42, 'Dirty read');
--Do not COMMIT TRANSACTION or ROLLBACK TRANSACTION here

Query 2 - Read the rows including the open transaction:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.demo;

Returns:

col1 col2

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

99 Normal transaction

42 Dirty read

P.S.: Don't forget to clean up this demo data:

COMMIT TRANSACTION;
DROP TABLE dbo.demo;
GO

SQL Server Try_Convert Function

It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

TRY_CONVERT() returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Data_type - The datatype into which to convert. Here length is an optional parameter which helps to get result in specified length.

Expression - The value to be convert

Style - style accepts the same values as the style parameter of the CONVERT function.

[ For more information, see CAST and CONVERT (Transact-SQL). ]

DECLARE @sampletext AS VARCHAR(10);
SET @sampletext = '123456';

DECLARE @realDate AS VARCHAR(10);
SET @realDate = '12/09/2015';

SELECT TRY_CONVERT(INT, @sampletext); -- 123456
SELECT TRY_CONVERT(DATETIME, @sampletext); -- NULL
SELECT TRY_CONVERT(DATETIME2, @realDate); -- 2015-12-09 00:00:00.0000000

Here is more example

SET DATEFORMAT dmy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --NULL
GO
SET DATEFORMAT mdy;  
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;  --2010-12-31 00:00:00.0000000
GO

TRY PARSE - Converting data types

It converts string data type to target data type(Date or Numeric).

For example, source data is string type and we need to covert to date type. If conversion attempt fails it returns NULL value.

Syntax: TRY_PARSE (string_value AS data_type [ USING culture ])

String_value – This is argument is source value which is NVARCHAR(4000) type.

Data_type – This argument is target data type either date or numeric.

Culture – It is an optional argument which helps to convert the value to in Culture format. Suppose you want to display the date in French, then you need to pass culture type as ‘Fr-FR’. If you will not pass any valid culture name, then PARSE will raise an error.

Example:

DECLARE @fakeDate AS VARCHAR(10);
DECLARE @realDate AS VARCHAR(10);

SET @fakeDate = 'iamnotadate';
SET @realDate = '13/09/2015';

SELECT TRY_PARSE(@fakeDate AS DATE); --NULL as the parsing fails
SELECT TRY_PARSE(@realDate AS DATE); -- NULL due to type mismatch
SELECT TRY_PARSE(@realDate AS DATE USING 'Fr-FR'); -- 2015-09-13

SQL Server Index Overview

A table or view can contain the following types of indexes:

• Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.  
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

• Nonclustered

• Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.

• The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

Create Clustered index:

CREATE TABLE Employees
(
ID CHAR(900),
FirstName NVARCHAR(3000),
LastName NVARCHAR(3000),
StartYear CHAR(900)
)
GO
CREATE CLUSTERED INDEX IX_Clustered

Create Non-Clustered index:

CREATE TABLE Employees
(
ID CHAR(900),
FirstName NVARCHAR(3000),
LastName NVARCHAR(3000),
StartYear CHAR(900)
)
GO
CREATE NONCLUSTERED INDEX IX_NonClustered
ON Employees(StartYear)
GO

Show index info:

SP_HELPINDEX tablename

Drop index:

DROP INDEX IX_NonClustered ON Employees

Returns size and fragmentation indexes:

sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }``
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
Example:
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorks2017'), OBJECT_ID(N'IX_Address_StateProvinceID'), NULL, NULL , 'DETAILED');

Rebuild or reorganize all indexes on a table

avg_fragmentation_in_percent value Corrective statement

>5% and < = 30% REORGANIZE

>30%                     REBUILD

Rebuilding indexes is done using the following statement

ALTER INDEX All ON tableName REBUILD;

This drops the index and recreates it, removing fragementation, reclaims disk space and reorders index pages. 

One can also reorganize an index using

ALTER INDEX All ON tableName REORGANIZE;

which will use minimal system resources and defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes.

Rebuild all index database

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

SQL Server PatIndex

 Returns the starting position of the first occurrence of a the specified pattern in the specified expression.

Parameters:

1. pattern. A character expression the contains the sequence to be found. Limited to A maximum length of

8000 chars. Wildcards (%, _) can be used in the pattern. If the pattern does not start with a wildcard, it may only match whatever is in the beginning of the expression. If it doesn't end with a wildcard, it may only match whatever is in the end of the expression.

2. expression. Any string data type.

Example:

SELECT PATINDEX('%ter%', 'interesting') -- Returns 3.
SELECT PATINDEX('%t_r%t%', 'interesting') -- Returns 3.
SELECT PATINDEX('ter%', 'interesting') -- Returns 0, since 'ter' is not at the start.
SELECT PATINDEX('inter%', 'interesting') -- Returns 1.
SELECT PATINDEX('%ing', 'interesting') -- Returns 9.

s

SQL Server Replace()

 Returns a string (varchar or nvarchar) where all occurrences of a specified sub string is replaced with another substring.

Parameters:

1. string expression. This is the string that would be searched. It can be a character or binary data type.

2. pattern. This is the sub string that would be replaced. It can be a character or binary data type. The pattern argument cannot be an empty string.

3. replacement. This is the sub string that would replace the pattern sub string. It can be a character or binary data.

SELECT REPLACE('This is my string', 'is', 'XX') -- Returns 'ThXX XX my string'.

Notes:

• If string expression is not of type varchar(max) or nvarchar(max), the replace function truncates the return value at 8,000 chars.
• Return data type depends on input data types - returns nvarchar if one of the input values is nvarchar, or varchar otherwise.
• Return NULL if any of the input parameters is NULL

Soundex in SQL Server

 Returns a four-character code (varchar) to evaluate the phonetic similarity of two strings.

Parameters:

1. character expression. An alphanumeric expression of character data.

The soundex function creates a four-character code that is based on how the character expression would sound when spoken. the first char is the the upper case version of the first character of the parameter, the rest 3 characters are numbers representing the letters in the expression (except a, e, i, o, u, h, w and y that are ignored).

Example:

SELECT SOUNDEX ('Smith') -- Returns 'S530'
SELECT SOUNDEX ('Smythe') -- Returns 'S530'

SQL Server Format() Function in detail with example

Returns a NVARCHAR value formatted with the specified format and culture (if specified). This is primarily used for converting date-time types to strings.

Parameters:

1. value. An expression of a supported data type to format. valid types are listed below.

2. format. An NVARCHAR format pattern. See Microsoft official documentation for standard and custom format strings.

3. culture. Optional. nvarchar argument specifying a culture. The default value is the culture of the current session.

DATE

DECLARE @d DATETIME = '2021-07-31';
SELECT
 FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result' -- Returns '7/31/2021'
,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31/07/2021'
,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result' -- Returns '31.07.2021'
,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result' -- Returns '2021/7/31'
,FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result' -- Returns 'Saturday, July 31, 2021'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result' -- Returns '31 July 2021'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result' -- Returns 'Samstag, 31. Juli 2021'

Using custom format strings:

SELECT 
 FORMAT ( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result' -- Returns '31/07/2021'
,FORMAT (123456789,'###-##-####') AS 'Custom Number Result' -- Returns '123-45-6789'
,FORMAT ( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','en-US') AS 'US' -- Returns 'Saturday, July 31, 2021 12:00:00 AM'
,FORMAT ( @d,'dddd, MMMM dd, yyyy hh:mm:ss tt','hi-IN') AS 'Hindi' -- Returns 'शनिवार, जुलाई 31, 2021 12:00:00 पूर्वाह्न'
,FORMAT ( @d, 'dddd', 'en-US' ) AS 'US' -- Returns 'Saturday'
,FORMAT ( @d, 'dddd', 'hi-IN' ) AS 'Hindi' -- Returns 'शनिवार'

FORMAT can also be used for formatting CURRENCY,PERCENTAGE and NUMBERS.

CURRENCY

DECLARE @Price1 INT = 40
SELECT 
 FORMAT (@Price1,'c','en-US') AS 'CURRENCY IN US Culture' -- Returns '$40.00'
,FORMAT (@Price1,'c','de-DE') AS 'CURRENCY IN GERMAN Culture' -- Returns '40,00 €'

We can specify the number of digits after the decimal.

DECLARE @Price DECIMAL(5,3) = 40.356
SELECT 
FORMAT( @Price, 'C') AS 'Default',        -- Returns '$40.36'
FORMAT( @Price, 'C0') AS 'With 0 Decimal', -- Returns '$40'
FORMAT( @Price, 'C1') AS 'With 1 Decimal', -- Returns '$40.4'
FORMAT( @Price, 'C2') AS 'With 2 Decimal' -- Returns '$40.36'

PERCENTAGE

DECLARE @Percentage float = 0.35674
SELECT 
FORMAT( @Percentage, 'P') AS '% Default', -- Returns '35.67 %'
FORMAT( @Percentage, 'P0') AS '% With 0 Decimal', -- Returns '36 %'
FORMAT( @Percentage, 'P1') AS '% with 1 Decimal' -- Returns '35.7 %'

NUMBER

DECLARE @Number AS DECIMAL(10,2) = 454545.389
SELECT 
FORMAT( @Number, 'N','en-US') AS 'Number Format in US', -- Returns '454,545.39'
FORMAT( @Number, 'N','en-IN') AS 'Number Format in INDIA', -- Returns '4,54,545.39'
FORMAT( @Number, '#.0') AS 'With 1 Decimal', -- Returns '454545.4'
FORMAT( @Number, '#.00') AS 'With 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '#,##.00') AS 'With Comma and 2 Decimal', -- Returns '454,545.39'
FORMAT( @Number, '##.00') AS 'Without Comma and 2 Decimal', -- Returns '454545.39'
FORMAT( @Number, '000000000') AS 'Left-padded to nine digits' -- Returns '000454545'

Important Notes:

• FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.
• FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR).
• FORMAT relies upon CLR formatting rules which dictate that colons and periods must be escaped. Therefore, when the format string (second parameter) contains a colon or period, the colon or period must be escaped with backslash when an input value (first parameter) is of the time data type.

Related Article:

Date and Time formatting using Format() function in SQL Server

SQL Server QUOTENAME() Function

 The QUOTENAME() function returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.

Returns a Unicode string surrounded by delimiters to make it a valid SQL Server delimited identifier.

Parameters:

1. character string. A string of Unicode data, up to 128 characters (sysname). If an input string is longer than 128 characters function returns null.

2. quote character. Optional. A single character to use as a delimiter. Can be a single quotation mark (' or ``), a left or right bracket ({,[,(,< or >,),],}) or a double quotation mark ("). Any other value will return null. Default value is square brackets.

Example:

SELECT QUOTENAME('what''s my name?')	  -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', '[') -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', ']') -- Returns [what's my name?]
SELECT QUOTENAME('what''s my name?', '''') -- Returns 'what''s my name?'
SELECT QUOTENAME('what''s my name?', '"') -- Returns "what's my name?"
SELECT QUOTENAME('what''s my name?', ')') -- Returns (what's my name?)
SELECT QUOTENAME('what''s my name?', '(') -- Returns (what's my name?)
SELECT QUOTENAME('what''s my name?', '<') -- Returns <what's my name?>
SELECT QUOTENAME('what''s my name?', '>') -- Returns <what's my name?>
SELECT QUOTENAME('what''s my name?', '{') -- Returns {what's my name?}
SELECT QUOTENAME('what''s my name?', '}') -- Returns {what's my name?}
SELECT QUOTENAME('what''s my name?', '`') -- Returns `what's my name?`

SQL Server Indexed VIEW

 To create a view with an index, the view must be created using the WITH SCHEMABINDING keywords.

Creating indexed views differs from creating normal views in that using the SCHEMABINDING hint is not optional. This means that you will not be able to apply structure changes on the tables that may affect the indexed view unless you alter or drop that indexed view first. 

In addition, you need to specify two parts name of these tables including the schema with the table name in the view definition. Also, any user-defined function that is referenced by the created indexed view should be created using WITH SCHEMABINDING hint.

Once the Indexed view is created, its data will be stored in your database the same as any other clustered index, so the storage space for the view’s clustered index should be taken into consideration.

You can benefit from indexed views if its data is not frequently updated, as the performance degradation of maintaining the data changes of the indexed view is higher than the performance enhancement of using this Indexed View. 

Indexed views improve the performance of queries that use joins and aggregations in processing huge amount of data and are executed very frequently. The environments that are best suited to indexed views are data warehouses and the Online Analytical Processing (OLAP) databases.

CREATE VIEW view_EmployeeInfo
WITH SCHEMABINDING
AS
SELECT EmployeeID,
        FirstName,
        LastName,
        HireDate
FROM [dbo].Employee

Any clustered or non-clustered indexes can be now be created:

CREATE UNIQUE CLUSTERED INDEX IX_view_EmployeeInfo
ON view_EmployeeInfo
(
    EmployeeID ASC
)

There Are some limitations to indexed Views:

• The view definition can reference one or more tables in the same database.
• Once the unique clustered index is created, additional nonclustered indexes can be created against the view.
• You can update the data in the underlying tables – including inserts, updates, deletes, and even truncates.
• You can’t modify the underlying tables and columns. The view is created with the WITH SCHEMABINDING option.
• It can’t contain COUNT, MIN, MAX, TOP, outer joins, or a few other keywords or elements. 

Generating a Date Range With a Tally Table

Another way you can generate a range of dates is by utilizing a Tally Table to create the dates between the range:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With
    E1(N) As (Select 1 From (Values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) DT(N)),
    E2(N) As (Select 1 From E1 A Cross Join E1 B),
    E4(N) As (Select 1 From E2 A Cross Join E2 B),
    E6(N) As (Select 1 From E4 A Cross Join E2 B),
    Tally(N) As
    (
        Select Row_Number() Over (Order By (Select Null))
        From E6
    )
    Select DateAdd(Day, N - 1, @FromDate) Date
    From Tally
    Where N <= DateDiff(Day, @FromDate, @ToDate) + 1

 Related Article: Generating Date Range With Recursive CTE

Generating Date Range With Recursive CTE

 Using a Recursive CTE, you can generate an inclusive range of dates:

Declare @FromDate Date = '2014-04-21',
        @ToDate Date = '2014-09-02'
;With DateCte (Date) As
(
    Select @FromDate Union All
    Select DateAdd(Day, 1, Date)
    From DateCte
        Where Date < @ToDate
)
Select Date
    From DateCte
Option (MaxRecursion 0)

The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option. Otherwise it will throw below error.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

(MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether.

Related Article : Generating a Date Range With a Tally Table

With Ties Option in SQL Server

When we use with Ties Option, SQL Server Outputs all the Tied rows irrespective of limit we impose.

Let's understand with below example.

Test data:

CREATE TABLE #TEST
(
Id INT,
Name VARCHAR(10)
)
Insert Into #Test
select 1,'A'
Union All
Select 1,'B'
union all
Select 1,'C'
union all
Select 2,'D'

Output:

Id	Name
1	A
1	B
1	C
2	D

Let's Check with out With Ties Option

Select Top (1) Id, Name From
#TEST
Order By Id ;

Output: (Output of above query not guaranteed every time. )

Id	Name
1	B

Let's run same query With Ties Option

Select Top (1) With Ties Id, Name From
#test
Order By Id ;

Output: ( 1 is present 3 times in the table )

Id	Name
1	A
1	B
1	C

Here is some more example:

Select Top (1) With Ties Id, Name From
#test
Order By Id ;

Output: 

Id	Name
1	A

Information about SQL Server version

 Try using below sql script to find the detail information about SQL server Version.

SELECT SERVERPROPERTY('MachineName') AS Host,
    SERVERPROPERTY('InstanceName') AS Instance,
    DB_NAME() AS DatabaseContext,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    CASE SERVERPROPERTY('IsClustered')
        WHEN 1 THEN 'CLUSTERED'
    ELSE 'STANDALONE' END AS ServerType,
@@VERSION AS VersionNumber;

Output:

Find Column Name From All Tables of Database

Find the column name present in all tables in the database. You use below query to find out.

For Example Column Name: Id present in list of tables in database

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
        where c.name like 'Column Name%'
    ORDER BY schema_name, table_name;

Output:

How to search through Stored procedures in SQL Server

 You can use below query to search through Stored procedures in SQL Server. It will help to find part of string present in stored procedure.

SELECT o.type_desc AS ROUTINE_TYPE,o.[name] AS ROUTINE_NAME,
m.definition AS ROUTINE_DEFINITION
    FROM sys.sql_modules AS m INNER JOIN sys.objects AS o
        ON m.object_id = o.object_id WHERE m.definition LIKE '%RK From SP Modified%'
    order by ROUTINE_NAME

Output: