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.
You can use below query to find total number of Table in your database.
select COUNT(*) from INFORMATION_SCHEMA.TABLES
Liked By
Write Answer
Count number of tables in sql server
We use cookies to ensure you have the best browsing experience on our website. By using our site, you
acknowledge that you have read and understood our
Cookie Policy &
Privacy Policy
Join MindStick Community
You have need login or register for voting of answers or question.
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