Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to count total number of stored procedure and tables in SQL Server 2008

I have database Test1 in SQL Server 2008 R2. On the live server I took backup from there and restore it at our local machine as Test2 and added some tables and procedures.

If we restore Test2 back onto the live server so is it any query which can get tables name and procedure name which is only in test 2 not in test 1 or SQL Server treated it as totally different database?

And what is the query if I want to know only the number of difference of Test1 and Test2 databases

like image 885
Shashank Avatar asked Oct 11 '13 09:10

Shashank


People also ask

How can get total count of stored procedure in SQL Server?

You can find in sys. objects all types of objects in the database. You will have to run this query on each of your databases to see the count of objects. You can find all information about what is stored in sys.

How do I count the number of tables in a SQL Server database?

INFORMATION_SCHEMA. TABLES returns one row for each table in the current database for which the current user has permissions. As of SQL Server 2008, you can also use sys. tables to count the the number of tables.

How many procedures are there in SQL Server?

There are two types of stored procedures available in SQL Server: User defined stored procedures. System stored procedures.

What is the most performant way to get the total number of records from a table?

With the help of the SQL count statement, you can get the number of records stored in a table.


2 Answers

This will give you the count of tables and stored procedures.

SELECT      CASE 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') GROUP BY TYPE 

You can find in sys.objects all types of objects in the database. You will have to run this query on each of your databases to see the count of objects.

You can find all information about what is stored in sys.objects here.

like image 59
Radu Gheorghiu Avatar answered Oct 12 '22 21:10

Radu Gheorghiu


You can use those 2 queries:

select count(*) as TablesCount from sys.tables select count(*) as ProceduresCount from sys.procedures 
like image 31
Szymon Avatar answered Oct 12 '22 22:10

Szymon