Qw7u1hbh

1CREATE TABLE #Results (geom)
2
3    SET NOCOUNT ON
4
5    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
6    SET  @TableName = ''
7    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
8
9    WHILE @TableName IS NOT NULL
10    BEGIN
11        SET @ColumnName = ''
12        SET @TableName = 
13        (
14            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
15            FROM    INFORMATION_SCHEMA.TABLES
16            WHERE       TABLE_TYPE = 'BASE TABLE'
17                AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
18                AND OBJECTPROPERTY(
19                        OBJECT_ID(
20                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
21                             ), 'IsMSShipped'
22                               ) = 0
23        )
24
25        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
26        BEGIN
27            SET @ColumnName =
28            (
29                SELECT MIN(QUOTENAME(COLUMN_NAME))
30                FROM    INFORMATION_SCHEMA.COLUMNS
31                WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
32                    AND TABLE_NAME  = PARSENAME(@TableName, 1)
33                    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
34                    AND QUOTENAME(COLUMN_NAME) > @ColumnName
35            )
36
37            IF @ColumnName IS NOT NULL
38            BEGIN
39                INSERT INTO #Results
40                EXEC
41                (
42                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
43                    FROM ' + @TableName + ' (NOLOCK) ' +
44                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
45                )
46            END
47        END 
48    END
49
50    SELECT ColumnName, ColumnValue FROM #Results
51END
52	
53DO $$
54DECLARE
55  value int := 0;
56  sql text := 'The constructed select statement';
57  rec1 record;
58  rec2 record;
59BEGIN
60  DROP TABLE IF EXISTS _x;
61  CREATE TEMPORARY TABLE _x (
62    schema_name text, 
63    table_name text, 
64    column_name text,
65    found text
66  );
67  FOR rec1 IN 
68        SELECT table_schema, table_name, column_name
69        FROM information_schema.columns 
70        WHERE table_name <> '_x'
71                AND UPPER(column_name) LIKE UPPER('%%')                  
72                AND table_schema <> 'pg_catalog'
73                AND table_schema <> 'information_schema'
74                AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
75        LOOP
76    sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%%' , ''')');
77    RAISE NOTICE '%', sql;
78    BEGIN
79        FOR rec2 IN EXECUTE sql LOOP
80            RAISE NOTICE '%', sql;
81            INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
82        END LOOP;
83    EXCEPTION WHEN OTHERS THEN
84    END;
85  END LOOP;
86  END; $$;
87
88SELECT * FROM _x;