all the tables where rows are not null from database

In SQL server 2008,I want all the tables whose row cou开发者_运维问答nt is not NULL Can somebody help out?

---

Very quick and dirty way:

In SSMS, right-click on database, Select Reports-> Standard Reports -> Disk Usuage by Top Tables

---

Another way would be to use the undocumented procedure sp_MSForEachTable

Create Table ##TempRowCount
(
TableName nvarchar(max),
NumberOfRows int
)
Exec sp_msforeachTable 'Insert Into ##TempRowCount select ''?'', count(*) From ?'

Select * From ##TempRowcount
Where NumberOfRows > 0

Drop Table ##TempRowCount

This may take some time to run depending on the size of your database and tables.

---

A quick and dirty way (includes effects of uncommitted transactions)

SELECT OBJECT_NAME(p.object_id), SUM(rows)
FROM sys.partitions p 
WHERE index_id < 2 and OBJECTPROPERTYEX (object_id ,'IsUserTable' ) = 1
GROUP BY p.object_id
HAVING SUM(rows) > 0