get total number of tables in a database in SQL

How to get total number of tables in a database in SQL?

It might happen that you will have to start working on a big database, someday. So, just out of curiosity if you ever want to find total number of tables that are in your table you can use this simple query:
 

SELECT COUNT(*) as TotalTables FROM sys.tables WHERE type in ('u')