A quick little tip I found a couple of years ago. Ever use the count(*) syntax and it takes forever to count all the rows in a table? I had this problem when I would sometimes run counts on a bunch of tables to check that my replication was working correctly. Here is a much, much quicker way using the DMV sys.dm_db_partition_stats:

SELECT
	SUM(row_count) AS TotRows
FROM 
	sys.dm_db_partition_stats
WHERE 
	object_name(object_id) = 'YourTableName'
	AND index_id < 2

Running the count(*) on one of my large tables took 33 seconds. The above statement took 1 second.

If you want to do a record count on all tables in a database:

SELECT 
	OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM 
	sys.dm_db_partition_stats st
WHERE 
	index_id < 2
    ORDER BY st.row_count DESC

Another little known way to get table counts quickly is in SSMS click View -> Object Explorer Details, then click on the “Tables” folder for any database. By default you will see all the table names, but not the row count. To get that, right-click on a column header and select “Row Count”.