Database tables and rows

Sometime our requirement to get database all tables with number of row.

Table without row

I have written below script to find out empty tables name.

All blank tables in a database.

CREATE TABLE #BlankTable(TableName varchar(255));

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN  

       SELECT @SQL = 'SELECT * into ##temp FROM [dbo].[' + RTRIM(@name) +']'

       EXEC(@SQL)

       IF (NOT EXISTS(SELECT * FROM ##temp))

       BEGIN

              INSERT INTO  #BlankTable(TableName) VALUES(@name)   

              PRINT 'Table Name: ' + @name

       END

       DROP TABLE ##temp  

       SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

SELECT * FROM #BlankTableDROP TABLE #BlankTable

Hope it will help you for find out the tables where no records saved.

How many rows in table

Below script to find the total number for records, indexes, size of a table in database.

SELECT

    t.NAME AS TableName,

    i.name as indexName,

    p.[Rows],

    sum(a.total_pages) as TotalPages,

    sum(a.used_pages) as UsedPages,

    sum(a.data_pages) as DataPages,

    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,

    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,

    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB

FROM

    sys.tables t

INNER JOIN     

    sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

    sys.allocation_units a ON p.partition_id = a.container_id

WHERE

    t.NAME NOT LIKE 'dt%' AND

    i.OBJECT_ID > 255 AND  

    i.index_id <= 1

GROUP BY

    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]

ORDER BY

    p.[Rows] desc

Hope it will help you

Keep sharing keep learning. Thank you