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
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.
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.
There are two types of stored procedures available in SQL Server: User defined stored procedures. System stored procedures.
With the help of the SQL count statement, you can get the number of records stored in a table.
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.
You can use those 2 queries:
select count(*) as TablesCount from sys.tables select count(*) as ProceduresCount from sys.procedures
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With