Showing posts with label Stored Procedure. Show all posts
Showing posts with label Stored Procedure. Show all posts

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