A lot of times I have to study a new database and count of rows per table has been a great source of info. There are many ways to get number of rows per table, but I’m sick of looking it up every time. So here goes one I prefer:
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.NAME AS TableName, Sum(p.[Rows]) as [RowCount], 'select top 1000 * from ['+SCHEMA_NAME(t.schema_id) +'].['+t.name+']' FROM sys.tables t INNER JOIN sys.partitions p ON t.[object_id] = p.[object_id] WHERE t.NAME NOT LIKE 'dt%' GROUP BY t.NAME,t.schema_id ORDER BY Sum(p.[Rows]) desc
Here is a discussion with more ways to do the same thing.