Thursday, June 15, 2006

Database integrity Audit Queries 1

Disclaimer: The same queries could be written in 1000000000000000000 oter ways. At some point of time I wrote them in this way. All queries here may not give you the output you want. For those I used spread shhed and wrote macros.


1.Total number of tables

SELECT COUNT(*) FROM all_objects WHERE owner='SCHEMA_NAME' AND object_type='TABLE'

2.Tables without PK

SELECT
object_name,object_type
FROM all_objects WHERE owner='SCHEMA_NAME' AND object_type='TABLE'
and
object_name not in (select distinct table_name from all_constraints ac
where
ac.owner='SCHEMA_NAME' and ac.CONSTRAINT_TYPE='P')

3.Total number of Pks

SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='P'
No. of tables with alternate keys SELECT a.TABLE_NAME,a.OWNER,
DECODE(COUNT(*),1,'X','Alternate Key exists') AS "alternate key Yes/No JOIN"
FROM
all_cons_columns a,all_constraints b
WHERE
a.TABLE_NAME=b.TABLE_NAME AND
a.OWNER=b.OWNER AND
a.CONSTRAINT_NAME=b.CONSTRAINT_NAME AND
a.owner='SCHEMA_NAME' AND
b.CONSTRAINT_TYPE='P'
GROUP BY a.TABLE_NAME,a.OWNER
ORDER BY a.table_name

4.Total no. of Fks


SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='R'
No. of un-indexed Fks SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
0 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
NOT EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)

5.Indexed vs. non indexed Fks

-- not having index
SELECT vw.table_name,vw.column_name,vw.CONSTRAINT_NAME,vw.position,
DECODE(vw.Y_N,0,'Not Indexed','Indexed') AS Index_Status
FROM
(SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
0 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
NOT EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)
UNION
-- having index
SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
1 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
EXISTS
(SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position)) vw


6.Total no. of Unique Key

SELECT COUNT(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='U'
Columns with length 1 SELECT table_name,column_name,data_type,data_length FROM all_tab_columns
WHERE
owner='SCHEMA_NAME'
AND
data_length=1
AND
(data_type LIKE '%CHAR%' OR data_type LIKE '%NUMBER%')

7.Number of nullable columns

a)select
ac.TABLE_NAME,ac.COLUMN_NAME
from all_tab_columns ac
where
ac.OWNER='SCHEMA_NAME' and ac.NULLABLE='Y'
order by 1
b)select
ac.TABLE_NAME,count(*)
from all_tab_columns ac
where
ac.OWNER='SCHEMA_NAME' and ac.NULLABLE='Y'
group by ac.TABLE_NAME
order by 1

8.No. of check constraints with In condition

SELECT
CONSTRAINT_NAME,TABLE_NAME,SEARCH_CONDITION
FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_type='C'

9.Total no. of constraint s

SELECT count(0)
FROM all_constraints ac
WHERE owner='SCHEMA_NAME'

10.Constraints with system generated name

a) SELECT count(0) FROM all_constraints ac WHERE owner='SCHEMA_NAME'
AND constraint_name LIKE 'SYS%'

b) SELECT constraint_type,count(constraint_name) FROM all_constraints ac
WHERE owner='SCHEMA_NAME' AND constraint_name LIKE 'SYS%'
group by constraint_type

10.Triggers

select
trg.TRIGGER_NAME,trg.TRIGGERING_EVENT,trg.TABLE_NAME
from all_triggers trg where trg.owner='SCHEMA_NAME'

11.Indexes

select * from all_ind_columns aic
where
aic.INDEX_OWNER='SCHEMA_NAME'


11.BMP IDX for FKs


select index_name,index_type from all_indexes where
index_type='BITMAP' and
index_name in
(select
index_name
from all_ind_columns where index_owner='SCHEMA_NAME' and column_name in
(
select v.column_name from
(
SELECT ucc.table_name,
ucc.column_name,
ucc.CONSTRAINT_NAME,
ucc.position,
1 AS Y_N
FROM
all_cons_columns ucc,
all_constraints uc,
all_tables ut
WHERE
ut.table_name=uc.table_name AND
uc.constraint_type='R'
AND
uc.constraint_name=ucc.constraint_name AND
ut.OWNER=uc.OWNER AND
uc.OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME' AND
EXISTS
(
SELECT 1 FROM
all_ind_columns uic
WHERE
ucc.table_name=uic.table_name AND
ucc.column_name=uic.column_name AND
uic.INDEX_OWNER=ucc.OWNER AND
ucc.OWNER='SCHEMA_NAME'
AND
ucc.position=uic.column_position))v))


No comments:

Post a Comment