How to make general search in Dashboard

Question

I have Search input in my dashboard how to make search value to look up in all tables in the database to get related for this search value using SQL server asp.net core what is the faster way I know that can do easily to get each The Dbset in Dbcontext and search in it but I want the faster way.

Sachin Singh · Answer

use strored procedure , if using EF map the store procedure to your Context , SP are always faster, below query will search your all table for a string value. DECLARE @SearchStr nvarchar(100) SET @SearchStr = '## YOUR STRING HERE ##' CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME( '%' + @SearchStr + '%' , '' '' ) WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ( 'char' , 'varchar' , 'nchar' , 'nvarchar' , 'int' , 'decimal' ) AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ' '' + @TableName + '.' + @ColumnName + '' ', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results DROP TABLE #Results

Amr Elshaer · Answer

Thanks, Sachin for helping me.

How to make general search in Dashboard

Answers (2)