The simple way to find out how many rows are in a table is to execute SELECT COUNT(*) FROM MyTable. Whilst this is fine for small tables, and is the only way to get a transactionally consistent result, it uses a table scan behind the scenes which may take some time and cause un-wanted IO on very large tables.
Some times a 'reasonable' figure is sufficient, in which case there is an alternative approach. For SQL Server 2005 onwards, the sys.dm_db_partition_stats DMV contains the row_count column that will provide this information. The figure is only reasonable, rather than transactionally consistent, because it is not updated after every INSERT or DELETE statement.
For earlier versions of SQL Server a similar result can be achieved using the sysindexes system table. The KB article "INF: Description of sysindexes and sp_spaceused" discusses sysindexes and explains why for performance reasons sysindexes is not updated until a checkpoint occurs, hence the rowcnt figure is not guaranteed.