To Get Objects/Table/Views/Procedure/index etc Count in Oracle and SQL

To Get Objects Count and Details in Oracle & SQL:

SQL:

To Get Objects Count:

SELECT 'Count' = COUNT(*), 'Type' = CASE type
                WHEN 'C' THEN 'CHECK constraints'
                WHEN 'D' THEN 'Default or DEFAULT constraints'
                WHEN 'F' THEN 'FOREIGN KEY constraints'
                WHEN 'FN' THEN 'Scalar functions'
                WHEN 'IF' THEN 'Inlined table-functions'
                WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints'
                WHEN 'L' THEN 'Logs'
                WHEN 'P' THEN 'Stored procedures'
                WHEN 'R' THEN 'Rules'
                WHEN 'RF' THEN 'Replication filter stored procedures'
                WHEN 'S' THEN 'System tables'
                WHEN 'TF' THEN 'Table functions'
                WHEN 'TR' THEN 'Triggers'
                WHEN 'U' THEN 'User tables'
                WHEN 'V' THEN 'Views'
                WHEN 'X' THEN 'Extended stored procedures'
    END
            , GETDATE()
    FROM sysobjects
    GROUP BY type
    ORDER BY type

To see all database tables and its rowcount:

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]

ORACLE:

-----Tables and its Count-----------


SELECT * FROM user_tables;

or

SELECT COUNT(*) FROM user_tables;



-----Objects and its Count-----------


SELECT * FROM user_objects

or

SELECT COUNT(*) FROM user_objects


-----Synonyms and its Count-----------


SELECT * FROM user_synonyms;

or

SELECT COUNT(*) FROM user_synonyms;


-----Views and its Count-----------


SELECT * FROM USER_VIEWS

or

SELECT COUNT(*) FROM USER_VIEWS


---------Indexes and its Count------------------

SELECT COUNT(*) FROM user_indexes;

or

SELECT COUNT(*) FROM user_indexes;


----------Packages and its Count------

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) =

'PACKAGE' order by OWNER, OBJECT_NAME

or

select Count(*) from SYS.ALL_OBJECTS where UPPER(OBJECT_TYPE) = 'PACKAGE'

order by OWNER, OBJECT_NAME

-----Triggers and its Count----------

select TRIGGER_NAME, OWNER from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME

or

select count(*)  from SYS.ALL_TRIGGERS order by OWNER, TRIGGER_NAME

-----Procedures and its Count----------------

select OWNER, OBJECT_NAME, ARGUMENT_NAME, DATA_TYPE, IN_OUT from

SYS.ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE

or

select count(*) from SYS.ALL_ARGUMENTS order by OWNER, OBJECT_NAME, SEQUENCE


--------Functions and its Count------------------

select OBJECT_NAME, OWNER from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) =

upper('FUNCTION') order by OWNER, OBJECT_NAME

or

select count(*) from SYS.ALL_OBJECTS where upper(OBJECT_TYPE) =

upper('FUNCTION') order by OWNER, OBJECT_NAME