Find a text in Database, Find string in database/all tables in SQL server

Below Query to search text in available procedures

SELECT name

FROM   sys.procedures

WHERE  Object_definition(object_id) LIKE '%MY_Search_String%'

This Procedure create a temp table and looking for searched string into all available tables

CREATE PROCEDURE usp_find_string_intables (@string AS VARCHAR(1000))
AS
BEGIN
	DECLARE @mincounter AS INT
	DECLARE @maxcounter AS INT
	DECLARE @stmtquery AS VARCHAR(1000)

	SET @stmtquery = ''

	CREATE TABLE #tmp (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		,rowid INT identity
		)

	CREATE TABLE #tablelist (
		tablename VARCHAR(128)
		,columnname VARCHAR(128)
		)

	DECLARE @tmp TABLE (name VARCHAR(128))
	DECLARE @tablename AS VARCHAR(128)
	DECLARE @columnname AS VARCHAR(128)

	INSERT INTO #tmp (
		tablename
		,columnname
		)
	SELECT a.name
		,b.name AS columnname
	FROM sysobjects a
	INNER JOIN syscolumns b ON a.name = object_name(b.id)
	WHERE a.type = 'u'
		AND b.xtype IN (
			SELECT xtype
			FROM systypes
			WHERE name = 'text'
				OR name = 'ntext'
				OR name = 'varchar'
				OR name = 'nvarchar'
				OR name = 'char'
				OR name = 'nchar'
			)
	ORDER BY a.name

	SELECT @maxcounter = max(rowid)
		,@mincounter = min(rowid)
	FROM #tmp

	WHILE (@mincounter <= @maxcounter)
	BEGIN
		SELECT @tablename = tablename
			,@columnname = columnname
		FROM #tmp
		WHERE rowid = @mincounter

		SET @stmtquery = 'select top 1  ' + '[' + @columnname + ']' + ' from ' +

                     '[' + @tablename + ']' + ' where ' + '[' + @columnname + ']' +

                     ' like ' + '''%' + @string + '%''';

		INSERT INTO @tmp (name)
		EXEC (@stmtquery)

		IF @@rowcount > 0
			INSERT INTO #tablelist
			VALUES (
				@tablename
				,@columnname
				)

		SET @mincounter = @mincounter + 1
	END

	SELECT *
	FROM #tablelist
END

FInd String in Database 

EXEC usp_find_string_intables 'Search Sring Place here'

Create Procedure for Pagination SQL Query

--==============================

Create Table Query

CREATE TABLE [dbo].[Student]

(

    Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    Name varchar(50) NOT NULL,

    Address varchar(100) NULL,

    Age int NOT NULL,

    Standard varchar(10) NOT NULL,

    [Percent] decimal(5, 2) NOT NULL,

    AddedOn datetime NOT NULL DEFAULT GETDATE(),

    Status bit NOT NULL

)

--==============================

Create Procedure for Insert Data

CREATE PROCEDURE [dbo].[sp_InsertStudent]   

    @Name       VARCHAR(50), 

    @Address        VARCHAR(100),   

    @Age        INT,

    @Standard     VARCHAR(10),

    @Percent        DECIMAL(5,2),

    @Status       BIT, 

    @Result     VARCHAR(50) OUTPUT,

    @CreatedId    INT OUTPUT     

AS           

BEGIN

    INSERT INTO Student(Name,Address,Age,Standard,[Percent],Status) VALUES (@Name,@Address,@Age,@Standard,@Percent,@Status) 

    SET @Result='Insert Successful'

    SET @CreatedId=@@IDENTITY

END

--===============================

CReate Procedure to get Student by Id

CREATE PROCEDURE [dbo].[sp_GetStudentById]  

    @Id           INT

AS           

BEGIN

    SELECT * FROM Student WHERE id=@Id

END

---=======================

CReate Procedure for Pagination wise data for Performance

CREATE PROCEDURE [dbo].[sp_GetStudent] 

    @PageNo       INT,

    @PageSize       INT

AS           

BEGIN

    DECLARE @qry  NVARCHAR(1000);   

    SET @qry='SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY AddedON DESC) AS ''RowNum'',* FROM Student WHERE 1=1) a WHERE a.RowNum BETWEEN ('+CAST(@pageNo AS NVARCHAR(5))+'-1)*'+CAST(@pageSize AS NVARCHAR(5))+'+1 AND ('+CAST(@pageNo AS NVARCHAR(5))+'*'+CAST(@pageSize AS NVARCHAR(5))+')'

    EXEC Sp_executesql @qry

END