Get the number of pages in a table on the database

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Sql server pages.

With the help of below sql script we will find the total number of pages in the server

--Get the list DataBase name DECLARE @tmpdatabase table (ID int IDENTITY (1,1), dbName varchar(100)) Insert into @tmpdatabase select name from  Master.sys.databases --select * from @tmpdatabase --Looping the Database DECLARE @FinalTable table ( RecordID int IDENTITY(1,1), ServerName varchar(100), DataBaseName varchar(100), tableName varchar(100), RowCounts int, TotalPages int, UsedPages int, UnusedPages int ) DECLARE @MinID int=1, @MaxID int = (select Count(*) from @tmpdatabase) while (@MinID<=5) BEGIN -- Total # of pages, used_pages, and data_pages for a given heap/clustered index declare @dName varchar(100) select @dName=dbName from @tmpdatabase where ID=@MinID DECLARE @s varchar(1000) set @s= 'SELECT     t.NAME AS TableName,     p.rows AS RowCounts,     SUM(a.total_pages) AS TotalPages,     SUM(a.used_pages) AS UsedPages,     (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages FROM     '+@dName+'.sys.tables t INNER JOIN         '+@dName+'.sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN     '+@dName+'.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN     '+@dName+'.sys.allocation_units a ON p.partition_id = a.container_id WHERE     t.NAME NOT LIKE ''dt%''     AND t.is_ms_shipped = 0     AND i.OBJECT_ID > 255 GROUP BY     t.Name, p.Rows ORDER BY     t.Name' DECLARE @Table table ( RecordID int IDENTITY(1,1), tableName varchar(100), RowCounts int, TotalPages int, UsedPages int, UnusedPages int ) insert into @Table exec (@s) Declare @TMin int=1,@Tmax int =(select Count(*) from @Table) WHILE (@TMin<=@Tmax) BEGIN DECLARE @ServerName varchar(100) , @DataBaseName varchar(100), @tableName varchar(100), @RowCounts int, @TotalPages int, @UsedPages int, @UnusedPages int select @tableName=tablename,@RowCounts=RowCounts, @TotalPages=TotalPages, @UsedPages=UsedPages,@UnusedPages=UnusedPages from @Table where RecordID=@TMin insert into @FinalTable(ServerName,DataBaseName,tableName,RowCounts,TotalPages, UsedPages,UnusedPages)values                                          (@@SERVERNAME,@dName,@tableName,@RowCounts,@TotalPages, @UsedPages,@UnusedPages) Set @Tmin=@Tmin+1 End SET @MinID=@MinID+1 End select * from @FinalTable