Search a record in all Postgresql tables

Question

Search a record in all Postgresql tables

Navigation

#1 by (1 votes)

0

I am currently looking for a query or a script that allows me to search for a word among all the tables within the same database, searching the internet for this code:

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

But I execute it and it gives me error ... Do some of you know if there is any search for a data between all the tables?

postgresql

asked by Luis Alfredo Serrano Díaz 07.05.2018 в 21:01

source

1 answer

Segment violation (core generated) ion-select does not show full text Ionic 3

score 1 · Answer 1

It would be something like this:

CREATE OR REPLACE FUNCTION buscarCadena(cadena character varying, esquema character varying) RETURNS 
 character varying AS $$
DECLARE
 tabla character varying;
 columna character varying;
 r record;
    BEGIN
    FOR tabla IN
        select table_name from information_schema.tables where table_schema = esquema 
    LOOP
        FOR columna IN
            SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' 
                AND table_name = tabla and data_type = 'character varying'
        LOOP
            FOR r IN EXECUTE format('SELECT 1 FROM %I where %I = ''%I''', tabla,columna,cadena)
            LOOP
                return 'tabla: '|| tabla||' || columna: '||columna;
            END LOOP;

        END LOOP;
    END LOOP;
    return 'No encontrada';
    END;
$$ LANGUAGE plpgsql;

This would return the first table + match column. To call it would be:

select buscarCadena('cadena', 'public');

I hope you serve, greetings!