Get total number of rows in a table without SELECT count(*)


--Option 1:
SELECT  OBJECT_SCHEMA_NAME(object_id)+'.'+ OBJECT_NAME(object_id) AS  [TableName]
, SUM(dmps.row_count) AS [RowCount]
 FROM sys.dm_db_partition_stats AS dmps
 WHERE OBJECTPROPERTY(object_id, N'IsUserTable') = 1
-- And object_id = OBJECT_ID('mytablename')
Group by OBJECT_SCHEMA_NAME(object_id) +'.'+ OBJECT_NAME(object_id)

--Option 2
--2.1 Get rows from all user tables
SELECT OBJECT_NAME(object_id) AS Name, SUM(rows) AS TotalCnt
FROM sys.partitions
WHERE index_id in (0,1)
aND OBJECTPROPERTY(object_id, 'IsUserTable') = 1
GROUP BY object_id

--2.2 Get row counts for a table
SELECT OBJECT_NAME(object_id) AS TableName, SUM(rows) AS TotalRows
FROM sys.partitions
WHERE index_id in (0,1) AND object_id = OBJECT_ID('mytablename')
GROUP BY object_id

--Option 3
--3.1
DBCC CHECKTABLE ("yourtablename")
--3.2
DBCC CHECKDB

--Option 4
  SELECT  OBJECT_SCHEMA_NAME(object_id)+'.'+ OBJECT_NAME(object_id) AS  [TableName]
, SUM(rows) AS [RowCount]
 FROM [sys].[system_internals_partitions]
WHERE OBJECTPROPERTY(object_id, N'IsUserTable') = 1
-- And object_id = OBJECT_ID('mytablename')
Group by OBJECT_SCHEMA_NAME(object_id) +'.'+ OBJECT_NAME(object_id)

--Option 5
select so.[name] as TableName, max(si.[rows]) AS TotalRowCount
from sys.sysindexes si inner join sys.sysobjects  so
on si.[id] = so.[id]
where so.xtype = 'U' 
group by so.[name]

 

Option 6: Powershell with SQL Server Provider:


 Import-Module -Name 'SQLPS' -DisableNameChecking 



$svrname = "MC\MSSQL2014" 
$mydbName="myDB1"
$myTableName="mylog"

$myTablePath= "SQLSERVER:\SQL\$svrname\Databases\$mydbName\Tables"
#$_.name -match
#$_.name -eq
Get-ChildItem $myTablePath | ?{$_.name -Like $myTableName}| Select-Object Name,RowCount | Format-Table –AutoSize
 

 

 

Option 7: Powershell with SMO:


 Import-Module -Name 'SQLPS' -DisableNameChecking 
 

$myDBName ="myDB1"
$mytableName ="mylog"

$svrname = "MC\MSSQL2014" 
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 

$db = $srv.Databases.Item($myDBName)

$db.Tables.Item($mytableName).RowCount


 



 Import-Module -Name 'SQLPS' -DisableNameChecking


 
$myDBName ="myDB1"


$svrname = "MC\MSSQL2014" 
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 

$db = $srv.Databases.Item($myDBName)


foreach ( $t in $db.Tables ) { 

Try
{
Write-Host "Table: " $t.Name  "--- Cnt: " $t.RowCount
 
 
}
Catch [Exception]
 {
 write-host "  "$_.Exception.Message
 }
}


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s