CODEWITHSUNDEEP
sqlsql-server-2008
Pradeep
Pradeep

Reputation: 3468

all the tables where rows are not null from database

In SQL server 2008,I want all the tables whose row count is not NULL Can somebody help out?

Upvotes: 2

Views: 262

Answers (3)

Martin Smith
Martin Smith

Reputation: 453152

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

Upvotes: 2

codingbadger
codingbadger

Reputation: 43974

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300549

Very quick and dirty way:

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

Upvotes: 2

Related Questions