Some DMV's From Experts Site

1. Displays count of rows in each table

SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;

2. Files Names And Paths of All Databases In Current Intance
sELECT DB_NAME([database_id])AS [Database Name], [file_id],
name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;


SELECT OBJECT_NAME(object_id) AS [ObjectName], *
FROM sys.dm_db_partition_stats;

SELECT *
FROM sys.dm_exec_connections;

SELECT *
FROM sys.dm_exec_requests;

SELECT *
FROM sys.dm_exec_sessions;

SELECT *
FROM sys.dm_tran_database_transactions;

SELECT *
FROM sys.dm_tran_active_transactions