Sometime we need to count the no of table/view/indexes/stored procedure in Database.
--Returns Total No
of User Defined Table
select count(*) cntTables
from sysobjects
where type = 'U'
--Returns Total
No of User Defined View
select count(*) cntView from sysobjects where type = 'V'
--Returns Total No
of Index.You may need to further filter,
-- depending on
which types of indexes you want.
select count(*) cntIndex from sysindexes
--Returns No of
Stored Procredure
select Count(*) cntProc from sys.procedures
--Return numbers
of non clustered indexes on any table in entire database.
SELECT COUNT(i.TYPE) NoOfIndex,
[schema_name] = s.name, table_name = o.name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.TYPE IN ('U')
AND i.TYPE = 2
GROUP BY s.name, o.name
ORDER BY schema_name, table_name