1. Use DBCC UPDATEUSAGE - this updates the values of rows for each partition in a table.
2. Use undocumented stored procedure sp_msForEachTable and store the result set in a table.
Below is the query to get required output:
USE [DatabaseName]
GO
DECLARE @DynSQL NVARCHAR(255)
SET @DynSQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@DynSQL)
IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T (TableName nvarchar(500),NumberOfRows int)
GO
INSERT INTO #T
EXEC sp_msForEachTable 'SELECT PARSENAME(''?'', 1) as TableName,COUNT(*) as NumberOfRows FROM ?'
GO
SELECT * FROM #T ORDER BY NumberOfRows DESC
I like your blog, I read this blog please update more content on hacking,
ReplyDeleteTableau Online Training