SQL : Search text from all Tables of a database in SQL Server

SQL : Search text from all Tables of a database in SQL Server

Here is the store procedure which will search text from all the tables of a current database in microsoft sql server.

Store Procedure

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS 
BEGIN 
    
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') 
                                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 
 
 END

How to use SP

exec SearchAllTables 'myserchtext'


Hope this will help you and save your time.

Enjoy !!!

:)

Error : Could not find a part of the path 'bin\roslyn\csc.exe'

Error : Could not find a part of the path 'bin\roslyn\csc.exe'

Error

[error]C:\Program Files (x86)\Microsoft Visual Studio\2019\Enterprise\MSBuild\

Microsoft\VisualStudio\v16.0\Web\Microsoft.Web.Publishing.targets(2601,5): 

Error : Copying file bin\roslyn\csc.exe to 

obj\Release\Package\PackageTmp\bin\roslyn\csc.exe failed. 

Could not find a part of the path 'bin\roslyn\csc.exe'.

Solution

run this in the Package Manager Console:

Update-Package Microsoft.CodeDom.Providers.DotNetCompilerPlatform -r

Hope this will help you and save your time.

Enjoy !!!

:)