Friday, September 14, 2012

Find any NUMBER or TEXT from all DB


 These are the repository from where i can consult. Thanks for using this.

-----------------------------------------------------------------------------------------------------------------
Below Pl/Sql to find any NUMBER from all DB
-----------------------------------------------------------------------------------------------------------------
set serveroutput on size unlimited;

DECLARE
   v_id     NUMBER         := 1001249;
   v_sql1   VARCHAR (1000);
   cnt      NUMBER (10);
BEGIN
   FOR t IN (SELECT column_name, table_name
               FROM user_tab_columns
              WHERE column_name LIKE '%_ID' and data_type='NUMBER')
   LOOP
      v_sql1 :=
            'SELECT COUNT('
         || t.column_name
         || ') FROM '
         || t.table_name
         || ' WHERE '
         || t.column_name
         || ' = '
         || v_id;
      EXECUTE IMMEDIATE v_sql1
                   INTO cnt;
      IF (cnt > 0)
      THEN
         DBMS_OUTPUT.put_line ('..' || cnt);
         DBMS_OUTPUT.put_line ('..' || v_sql1);
      END IF;
   END LOOP;
END;
/




----------------------------------------------------------------------------------------------------------------
Below Pl/Sql to find any TEXT from all DB
---------------------------------------------------------------------------------------------------------------


SET serveroutput ON size unlimited;
DECLARE
  v_id   VARCHAR (1000) := '% is Invo%';
  v_sql1 VARCHAR (1000);
  cnt    NUMBER (10);
BEGIN
  FOR t IN
  (SELECT column_name,
    table_name
     FROM user_tab_columns
    WHERE data_type IN ('CHAR', 'NCHAR', 'NVARCHAR2', 'VARCHAR2')
  )
  LOOP
    v_sql1 :='SELECT COUNT(' || t.column_name || ') FROM '|| t.table_name|| ' WHERE '|| t.column_name|| ' like '''|| v_id||'''';
    EXECUTE IMMEDIATE V_SQL1 INTO CNT;
    IF (cnt > 0) THEN
      DBMS_OUTPUT.put_line ('..' || cnt);
      DBMS_OUTPUT.PUT_LINE ('..' || V_SQL1);
    END IF;
  END LOOP;
END;
/

2 comments:

  1. 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

    ReplyDelete
  2. Thanks Gurjeet for valuable feedback...!!!!

    ReplyDelete