Search all Tables in DB for a String but using Postgres - What is the difference

  • Hi Forum,

    I want to search a Database for a String value and I have found a script that works. Only thing is, I am using PostGres & am not very experienced in the difference between the two.

    The script is below so if someone can let me know what needs to change to get it to work in PostGres, it would help a lot. Thanks

    Thanks to Reto Egeter for this script.

    /* Reto Egeter, fullparam.wordpress.com */

    DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
    SET @SearchStrColumnValue = '%plains%' /* use LIKE syntax */
    SET @FullRowResult = 1
    SET @FullRowResultRows = 3
    SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
    SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
    SET @SearchStrInXML = 0 /* Searching XML data may be slow */

    IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
    CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
    SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
    DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

    WHILE @TableName IS NOT NULL
    BEGIN
    SET @TableName =
    (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
    IF @TableName IS NOT NULL
    BEGIN
    DECLARE @sql VARCHAR(MAX)
    SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
    AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
    AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
    AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END + ',COLUMN_NAME)'
    INSERT INTO @ColumnNameTable
    EXEC (@sql)
    WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
    BEGIN
    PRINT @ColumnName
    SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
    SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
    ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
    FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
    INSERT INTO #Results
    EXEC(@sql)
    IF @@ROWCOUNT > 0 IF @FullRowResult = 1
    BEGIN
    SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
    ' FROM ' + @TableName + ' (NOLOCK) ' +
    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
    EXEC(@sql)
    END
    DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
    END
    END
    END
    SET NOCOUNT OFF

    SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
    GROUP BY TableName, ColumnName, ColumnValue, ColumnType

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply