How many records has a table

Check this blog which contains a query to find the number of records in each table in the database in decreasing order:

http://blog.sqlauthority.com/2010/09/08/sql-server-find-row-count-in-table-find-largest-table-in-database-part-2/

If you're just interested in the top one then you can, of course, do:

SELECT TOP 1 ... rest of query