Recently, I was working in analyzing a healthcare database where one simple task was to identify the big tables in terms of “Number of records”. So. I needed to get the row count for each tables in a SQL Server Database.
There are many way to get this data. See the below T-SQL scripts:
1. Using MS SQL System Stored Procedure
1 2 3 4 5 6 7 8 9 10 11
USE [Test] CREATE TABLE #TableRowCounts ( TableName VARCHAR(255), [RowCount] INT ) EXEC sp_MSForEachTable 'INSERT #TableRowCounts (TableName, [RowCount]) SELECT ''?'', COUNT(*) FROM ?' SELECT TableName , [RowCount] FROM #TableRowCounts ORDER BY TableName, [RowCount] DESC DROP TABLE #TableRowCounts
When you run this query,It gives all the tables in the database with the table row count.
sp_MSforeachtable is a stored procedure that is used to apply a T-SQL command to every table that exists in the current database. Question mark (?) is used as the replacement of the table during the execution.
2. Using Dynamic Management Views
1 2 3 4 5 6 7 8 9
USE [Test] SELECT [TABLES].name AS [TableName], SUM([Partitions].[ROWS]) AS [RowCount] FROM sys.tables AS [TABLES] JOIN sys.partitions AS [Partitions] ON [TABLES].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) GROUP BY SCHEMA_NAME(schema_id), [TABLES].name;
If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.
Happy Learning 🙂
Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming. He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.