Tuesday, July 15, 2014

Count No of Table,View,Indexes,Stored Procedure

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