SQL Script - Search Text within database and find all tables having particular text


/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 26/09/2018 9:25:22 AM ******/
--Usage: EXECUTE [dbo].[SearchAllTables] 'SearchText'

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[SearchAllTables]
(@SearchStr NVARCHAR(100))
AS
BEGIN

    DECLARE @Results TABLE
    (
        ColumnName NVARCHAR(370)
      , ColumnValue NVARCHAR(3630)
      , ssql VARCHAR(255)
    );

    SET NOCOUNT ON;

    DECLARE @TableName    NVARCHAR(256)
          , @ColumnName   NVARCHAR(128)
          , @ColumnName2  NVARCHAR(128)
          , @SearchStr2   NVARCHAR(110)
          , @SearchStrSQL NVARCHAR(MAX);

    SET @TableName = N'';
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''');

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = N'';
        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
        );
        PRINT @TableName;
        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
                SET @ColumnName2 = REPLACE(@ColumnName, '''', '');

                --SET @SearchStrSQL = 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                --             FROM ' + @TableName + ' (NOLOCK) ' +
                --             ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

                -- PRINT @SearchStrSQL;

                INSERT INTO @Results
                EXEC ('SELECT DISTINCT ''' + @TableName + '.' + @ColumnName2 + ''', LEFT(' + @ColumnName + ', 3630)
, ''SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName2 + ' LIKE ''' + @SearchStr2 + '''''
                FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2);
            END;
        END;
    END;


    -- , ''SELECT * FROM ''' + @TableName + '.' + @ColumnName2 + ''' WHERE '' LEFT(' + @ColumnName + ', 3630) '' LIKE ' + @SearchStr2 + '''

    SELECT  ColumnName
          , ColumnValue
          , ssql
    FROM    @Results;
END;
GO


Comments

Post a Comment

Popular posts from this blog

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())         END AS [Age]               from [dbo] . [DimCustomer] c               order by BirthDate I am sure, there would be other better ways to do this and would love to hear, of how you have calculated age of the person
Read more

How to Troubleshoot Subscription issue in Reporting Services

Tips on troubleshooting Subscription issue in Reporting Services 1 – Look at the Reporting Services Log File Error Message from Log File: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;   INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed.  Success: False, Status: Failure writing file Startup :  The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup, .......      2 - Run below query against ReportServer Database and this 
Read more

MDX Queries - Current Day - Month and Year

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];
Read more