TABLE FINDER

Table_Finder is a procedure to find all the tables that are queried in a particular package or procedure. It takes Package name or procedure name as input parameter. Final output will be the list of table names that are queried by that package along with the table?s owner name. It finds the table names involved in all schemas. This developed procedure will be useful to all development and enhancement projects as it is gives output without consuming time.

CREATE OR REPLACE PROCEDURE table_finder(pkg_name IN VARCHAR) AS
CURSOR Test IS
 SELECT owner,referenced_name,object_type FROM user_dependencies ud,all_objects ao,user_synonyms us
  WHERE ud.name=pkg_name
  AND ud.referenced_type IN ( 'TABLE','SYNONYM')
  AND ud.referenced_owner NOT IN ('SYS','PUBLIC')
  AND ud.referenced_name=ao.object_name
  AND ud.referenced_name=us.synonym_name(+)
  AND ao.object_type LIKE'TABLE';
   
     BEGIN
           for rec in test
               loop
                      dbms_output.put_line(rec.owner);
                      dbms_output.put_line(rec.referenced_name);
                      dbms_output.put_line(rec.object_type);
               end loop;
      END;

[ Read More ]

Oracle PL/SQL Query for Pagination

Oracle PL/SQL function makes the input query statement such a way that the query out put returns for a start row index to end row index.

CREATE OR REPLACE FUNCTION pagination_stmt_fn 
 (
  i_query_statement   IN   VARCHAR2,
  i_start_index       IN   NUMBER,
  i_end_index         IN   NUMBER
 )
 RETURN VARCHAR2
 IS
 o_query_statement   VARCHAR2 (32000);
 v_start_index       NUMBER           := 0;
 v_end_index         NUMBER           := 0;
BEGIN
 IF i_start_index = 0
 THEN
  v_start_index := 1;
 ELSE
  v_start_index := i_start_index;
 END IF;

 IF i_end_index = 0
 THEN
  v_end_index := 10000000;
 ELSE
  v_end_index := i_end_index;
 END IF;

 o_query_statement :=
  ' select  * from (  '
    || '  select result.*, rownum row_num  from ( '
    || i_query_statement
    || ' ) result ) '
  || ' where row_num between '
  || TO_CHAR (v_start_index)
  || ' and '
  || TO_CHAR (v_end_index);

RETURN o_query_statement;

EXCEPTION
 WHEN OTHERS
 THEN
 RETURN NULL;
END pagination_stmt_fn;
/

/************************************************************************************************

--In put parameters
-- i_query_statement:  "SELELCT * FROM EMP ORDER BY EMPLOYEE_ID"   
   (assumption:   Table EMP is exist and contains some rows)

-- i_start_index: 10
--i_end_index: 25


select pagination_stmt_fn( 'SELECT * FROM EMP ORDER BY EMPLOYEE_ID',10,25) "pagination statement" from dual;


--out put on SQL* Plus window

pagination statement
--------------------------------------------------------------------------------
 select  * from (    select result.*, rownum row_num  from ( SELECT * FROM EMP 
ORDER BY EMPLOYEE_ID ) result )  where row_num between 10 and 25


*************************************************************************************************/

[ Read More ]

Oracle and SQL Server Database Search String procedure

This Procedure/Query is used to search list of strings available in Oracle/SQL server database. The strings to be searched should be passed as a parameter to the Procedure/Query. The results will be entered in a temporary table with the Table name and field name where the strings are found in the database.

CREATE PROCEDURE  strCheck 
(@strck varchar(100))

 AS

create table tbl3( num int)
create table TEMP_rec(tblName varchar(50),colName varchar(50))
Declare @tblName as varchar(25)
Declare @colName as varchar(25)
declare @colType as varchar(25)
declare @sql as varchar(200)

declare @num as int 
Declare tblcur cursor for 
select table_name as Name from INFORMATION_SCHEMA.Tables 
open tblcur 
 fetch next from tblcur into @tblName
while @@fetch_status=0

begin
 Declare colcur cursor for 
 SELECT column_Name,Data_Type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =   @tblName
   
 open colcur 
  fetch next from colcur into @colName,@coltype
  while @@fetch_status=0
  begin
   if @coltype='char' or @coltype='varchar'
   begin
    set @sql=''
    set @sql='select 1 from '
    select @sql = @sql + @tblName + ' where ' + @colname + ' like ' + '''%' + @strck + '%'''
    insert into tbl3 
     exec (@sql)
    if exists (select * from tbl3)
    begin
     delete from tbl3
     insert into TEMP_rec
      select @tblName,@colName
    end
   end
   fetch next from colcur into @colName,@coltype
  end
 close colcur
 deallocate colcur
    fetch next from tblcur into @tblName

end

close tblcur
deallocate tblcur
select * from TEMP_rec
drop table tbl3
drop table TEMP_rec
GO

[ Read More ]

Oracle Sql Queries to view dependencies and partitioning.

This is the list of queries to find out the dependencies between databse objects and to find out the partitioning details of a table.


1:

select * from dict

Description: It will give you the list of all the system tables that are available with the comments on how a particular table can help you.

2 :

SELECT *  FROM All_Dependencies WHERE owner = 'GLOS_DB' AND NAME LIKE '%XYZ%'

Description :  It describes dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.This view does not display the SCHEMAID column.

3:

SELECT * from all_source
where TYPE IN('PROCEDURE','PACKAGE','FUNCTION',)
AND TEXT LIKE '%XYZ%'

Description : It will give you the list of all those database objects which you have mentioned in the TYPE IN clause and where your table is being referred . There are total  7 types which are as follows :
1 FUNCTION
2 JAVA SOURCE
3 PACKAGE
4 PACKAGE BODY
5 PROCEDURE
6 TRIGGER
7 TYPE

4:

select * from all_mviews where Mview_name = 'schema name.MV_NAME'

Description: This will give you all the information related to your mentioned Materialized view like its last refresh_date, compile state etc.

5:

SELECT * FROM DBA_VIEWS WHERE TEXT LIKE '%XYZ%'

Description : It will give you the details of the views that are accessible to a DBA. Details such as its owner name, its type etc.



6:

select * from All_Views where Text like '%XYZ%'  AND TYPE LIKE 'VIEW'

Description : It will give you the details of the views that are accessible to users. Details such as its owner name, its type etc.

7:

select* from  DBA_DEPENDENCIES

Description : DBA_DEPENDENCIES describes all dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links.
For example :

SELECT type, name FROM SYS.DBA_DEPENDENCIES WHERE referenced_type   = 'TABLE' AND referenced_owner = 'GLOS_DB' AND referenced_name = 'GETS_GS_FINANCIAL_OASIS'AND type !=   'UNDEFINED'

8:

Select * from all_objects where Object_name like'%XYZ%'

Description : This will give you the information of all the objects that are accessible to the users.

9:

Select * from all_errors

Description : It describes current errors on all stored objects (views, procedures, functions, packages, and package bodies) accessible to the current user. It gives you the information like name of the object, type of the object, line in which error has came, position in the line where error occurred and text of the error.

10:

select TABLE_NAME from user_tab_cols where COLUMN_NAME like ' %XYZ%'

Description: This will give you the list of all the tables where the mentioned column is used.


Partitioning Related Queries :

1:

select * from all_part_tables

Description : It will list out all the tables that have been partitioned. It will give the information such as table name, partition type, sub partition type etc.

2:

select * from ALL_TAB_PARTITIONS where table_name like '˜XYZ'

Description : It will list out all the partitions and details of the partitions like partition name, sub partition count etc existing for  a table

3:

select * from All_Tab_Subpartitions where table_name like 'XYZ'

Description : It describes, for each table subpartition accessible to the current user, the subpartition name, name of the table and partition to which it belongs, and its storage attributes.

4:

select * from all_subpartition_templates

Description : It describes the subpartition templates accessible to the current user.

[ Read More ]