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

Create ORACLE Query to Get Concated string Values from relational table

Below Query will help to get the requery result from concated string.

Create Table Schema

  CREATE TABLE "TBL_VKYC_CRMDETAILS" 

   (

   "ID" NUMBER(10,0) NOT NULL ENABLE, 

"PRODUCTTYPE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"REFNO" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"CRMCASENO" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"CREATEDDATE" VARCHAR2(50 BYTE) NOT NULL ENABLE, 

"SQSREFID" VARCHAR2(100 BYTE) DEFAULT NULL, 

"CRMDOCRESPONSEID" VARCHAR2(100 BYTE) DEFAULT NULL, 

"SQSFILENAME" VARCHAR2(2000 BYTE) DEFAULT NULL

   );

CREATE SEQUENCE  SEQ_TBL_VKYC_CRMDETAILS

MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER TBL_VKYC_CRMDETAILS_SEQ 

BEFORE INSERT ON TBL_VKYC_CRMDETAILS 

FOR EACH ROW

BEGIN

  SELECT SEQ_TBL_VKYC_CRMDETAILS.NEXTVAL

  INTO   :new.id

  FROM   dual;

END;

/

ALTER TRIGGER TBL_VKYC_CRMDETAILS_SEQ ENABLE;

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

Like string is concatenated with comma seperate string/value, get the comma seperate value from relational table in rows, later get values for all rows and again convert into single column on ORACLE

Create table tblFirst(Id int, Name VARCHAR2(50), CompanyIds varchar2(50))

insert into tblFirst(id, Name, CompanyIds) values (1, 'Murli', '1')

insert into tblFirst(id, Name, CompanyIds) values (2, 'User 1', '1,2')

insert into tblFirst(id, Name, CompanyIds) values (3, 'User 2', '1,2')

insert into tblFirst(id, Name, CompanyIds) values (4, 'User 3', '1,2,3')

insert into tblFirst(id, Name, CompanyIds) values (5, 'User 0', '1,2')

select * from tblFirst

Commit

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

Create table tblCompany(Id int, CompanyName varchar2(50))

insert into tblCompany(id, CompanyName) values (1, 'Company A');

insert into tblCompany(id, CompanyName) values (2, 'Company B');

insert into tblCompany(id, CompanyName) values (3, 'Company C');

select * from tblCompany

Commit

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

select t1.*, (select  listagg(CompanyName,',')  WITHIN GROUP (ORDER BY 1) --product_variant_name, product_category,product_variant_id

from tblCompany C 

where C.id in

  (select regexp_substr(t1.CompanyIds,'[^,]+', 1, level) 

   from dual 

   connect BY regexp_substr(t1.CompanyIds, '[^,]+', 1, level) 

   is not null)) "Company Names" from tblFirst t1

order by t1.id desc

Output will look like this

Create Seperate Port from Core Appliation

  "Kestrel": {

    "EndPoints": {

      "Http": {

        "Url": "http://localhost:4300"

      }

    }

  },

Add Above set of lines on appsettings.json

Disable Link from HTML

  $('#lnkReInitiate').on('click', function () {

                if ($("#lnkReInitiate").hasClass("disabled-link")) {

                    return false;

                } else {

                    $("#lnkReInitiate").addClass("disabled-link");

                }

            });

 .disabled-link{

                cursor: default;

                pointer-events: none;        

                text-decoration: none;

                /*color: grey;*/

                background: gray;

            }

Get ErrorMessage from Code Behind in .Net

Below Code is to get the error message from server side 

foreach (BaseValidator v in Page.Validators)

                {

                    if (!v.IsValid)

                    {

                        Response.Write(v.ErrorMessage);

                    }

                }

To Avoid Deadlock

 private static readonly object lockObject = new object();

lock (lockObject)
{

}