Tuesday, June 4, 2019

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

No comments:

Post a Comment