You can count both tables and stored procedures by following query:
SELECTCASE TYPE
WHEN'U'THEN'User Defined Tables'WHEN'S'THEN'System Tables'WHEN'IT'THEN'Internal Tables'WHEN'P'THEN'Stored Procedures'WHEN'PC'THEN'CLR Stored Procedures'WHEN'X'THEN'Extended Stored Procedures'END,
COUNT(*)FROM SYS.OBJECTS
WHERE TYPE IN('U','P','PC','S','IT','X')GROUPBY TYPE
You can also query SELECT * FROM SYS.objects WHERE TYPE = 'U' to get the tables associated with a particular user in the database. Here TYPE='U' is the User Tables.
Prakash nidhi Verma
09-Jul-2018You can use INFORMATION_SCHEMA.TABLES for your database tables.
The following query will also return the number of table in your database:
OR
Vrahmdev Tiwari
09-Jul-2018you can use this simple query
SELECT COUNT(*) FROM SYS.TABLES
Note: It will return you the count of the tables in the database.
Anonymous User
25-Jan-2014Abhishek Singh
19-Jan-2014Shankar M
09-Feb-2013Amit Singh
29-Mar-2011for example:
USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'
Note: It will return you the count of the tables in the database.
Anonymous User
24-Mar-2011Hi Varun,
You can use below query to find total number of Table in your database.
select COUNT(*) from INFORMATION_SCHEMA.TABLES