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') 

  

Of course, first you have to connect to your database. Hope it helps.