Query to find row counts for all tables in a DB.

Row Counts for all Tables in a database:

select DB_NAME()+'.'+C.NAME+'.'+A.Name as [TableName],B.TotalRows from sys.objects A Inner Join
(Select sum(rows) as TotalRows,object_ID from sys.partitions group by object_Id) b on A.object_id=B.object_id
INNER JOIN Sys.schemas C on C.SCHEMA_ID=A.schema_id
where type= 'U' order by C.Name,A.Name

Note: I have seen some people mention online that the only accurate way of finding the row count is by doing count on the actual table itself.I could not verify this but may be something to keep in mind.

Advertisement

Query to find the Log File Internals

The following script helps in understanding the log file. It gives all the details about transaction log file such as no of VLF’s associated,log internal space usage,last log backup taken,DB recovery mode etc. I think this script helps in understanding the internal log file usage and can be used as a health check script.

/*Databases that are set to 'AutoClose' are not considered by dbcc sqlpref(logspace) and Offline databases are not considered.[FreeSpace_IN_LogFile_toShrink_InBytes] means the amount of shrinkable Log File space in bytes.It calculates this by summing the inactive VLF's from bottom up till it hits active VLF's(Status=2).This does not mean you *can* shrink the Log File.
Please use caution. This is only for informational purposes.
Also, added code to check SQL version because dbcc loginfo on sql 2012 and above returns one extra column.*/

If object_ID('Tempdb.dbo.#TempMain') is not null
Begin
Drop Table #TempMain
End

Create Table #TempMain (
SeqID int Identity(1,1),[Database Name] varchar(max),[Log Size(MB)] decimal(15,3),[Log Space Used(%)] decimal(15,3),[Status] int,UsedLogFileSize_InMB decimal(15,3),No_of_Active_VLFs int,
UnUsedLogFileSize_InMB decimal(15,3),No_Of_InActive_VLFs int,
ReasonFor_LogFile varchar(50),LastLogBackupTaken datetime,Db_RecoveryMode varchar(11),[FreeSpace_IN_LogFile_toShrink_InMB] decimal(15,3))

If object_ID('Tempdb.dbo.#Temp') is not null
Begin
Drop Table #Temp
End

Create Table #Temp (ID int Identity(1,1),RecoveryUnitId int,FileID tinyint,FileSize bigint,StartOffset varchar(max),FSeqNo int,[Status] tinyint,Parity tinyint,CreateLSN varchar(max))
declare @SQlLogSpace nvarchar(max)
set @SQlLogSpace = 'dbcc sqlperf(logSpace)'

Insert #TempMain([Database Name],[Log Size(MB)],[Log Space Used(%)],[Status])
Execute(@SQlLogSpace)

update #TempMain set Db_RecoveryMode =A.recovery_model_desc,
ReasonFor_LogFile=Log_reuse_wait_desc,LastLogBackupTaken=C.[LastLogBackupTime]
from Sys.databases A INNER JOIN #TempMain B on B.[Database Name]=A.Name
LEFT OUTER JOIN (Select max(backup_finish_date) as [LastLogBackupTime],database_name from msdb.dbo.backupset A
INNER JOIN Sys.databases B on B.Name=A.Database_Name
where type ='L' and B.recovery_model_desc in ('FULL','Bulk-Logged')
group by database_name) C on C.[database_Name]=A.Name

update #TempMain set UsedLogFileSize_InMB=cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3)),
unUsedLogFileSize_InMB=cast(([Log Size(MB)]) as decimal(20,3))-cast(cast(([Log Size(MB)]) as decimal(20,3))*cast(([Log Space Used(%)]/100) as decimal(20,3))as decimal(20,3))
from #TempMain

declare @a int,@b varchar(max)
select @a =min(SeqID) from #TempMain
While (@a>=1)

Begin
Select @b=[Database Name] from #TempMain where seqID=@a
declare @Sql nvarchar(max)
IF (Substring(cast(SERVERPROPERTY('productversion') as varchar(16)),1,
CHARINDEX('.',cast(SERVERPROPERTY('productversion') as varchar(16)),1)-1))>10
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( RecoveryUnitId,FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End
Else
Begin
Set @sql = 'USE ['+@B +'] declare @SQlLogInfo nvarchar(max)
set @SQlLogInfo = ''dbcc loginfo'' Insert into #Temp( FileID,FileSize,StartOffSet,FSeqNo,Status,Parity,CreateLSN)
Execute(@SQlLogInfo)'
End

Execute Sp_Executesql @SQL

Update #TempMain Set No_Of_InActive_VLFs =B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 0) A where #TempMain.SeqID=@a

Update #TempMain Set No_Of_Active_VLFs = B
From (Select sum(FileSize) as A ,Count(FileID) as B from #Temp where [status] = 2) A where #TempMain.SeqID=@a

Update #TempMain Set [FreeSpace_IN_LogFile_toShrink_InMB] = A
From (select cast((cast(sum(FileSize) as decimal(15,3))/1024.00/1024.00) as decimal(15,3)) as A from #Temp where ID>(Select Max(ID) from #Temp where Status=2)) A where #TempMain.SeqID=@a

Truncate table #Temp

IF (@a<(Select Max(seqId) from #TempMain))
Begin
Set @a=@a+1
continue
End
Else
Begin
select SeqID,[Database Name],Db_RecoveryMode,[Log Size(MB)],
[Log Space Used(%)],UsedLogFileSize_InMB,No_Of_Active_VLFs,
UnUsedLogFileSize_InMB,No_Of_InActive_VLFs,[FreeSpace_In_LogFile_ToShrink_InMB],
ReasonFor_LogFile,LastLogBackupTaken from #TempMain
Return
End
Drop table #TempMain
End