After deploying the sample warehouse available in Microsoft Fabric, I tried to check the number of records available in the deployed tables under the dbo schema. Surprisingly, the sys.partitions.count column has 0 values for all the tables associated with the respective schema (see post).
There are only a few tables available, and taking a record count for each table should be enough, which is relatively simple with the undocumented sp_MSForEachTable. Unfortunately, this approach doesn't work neither, so one needs to revert to the use of old-fashioned cursors (as I used to do in SQL Server 2000):
-- number of records via cursor DECLARE @table_name nvarchar(150) DECLARE @sql nvarchar(250) DECLARE @number_records bigint DECLARE @number_tables int, @iterator int DROP TABLE IF EXISTS dbo.#tables; CREATE TABLE dbo.#tables ( ranking int NOT NULL , table_name nvarchar(150) NOT NULL , number_records bigint ) INSERT INTO #tables SELECT row_number() OVER(ORDER BY object_id) ranking , concat(schema_name(schema_id),'.', name) table_name , NULL number_records FROM sys.tables obj WHERE obj.schema_id = schema_id('dbo') ORDER BY table_name SET @iterator = 1 SET @number_tables = IsNull((SELECT count(*) FROM #tables), 0) WHILE (@iterator <= @number_tables) BEGIN SET @table_name = (SELECT table_name FROM #tables WHERE ranking = @iterator) SET @sql = CONCAT(N'SELECT @NumberRecords = count(*) FROM ', @table_name) BEGIN TRY --get the number of records EXEC sp_executesql @Query = @sql , @params = N'@NumberRecords bigint OUTPUT' , @NumberRecords = @number_records OUTPUT IF IsNull(@number_records, 0)> 0 BEGIN SET @sql = 'UPDATE #tables' + ' SET number_records = ' + Str(@number_records) + ' WHERE table_name = ''' + @table_name + ''''; EXEC(@sql) END END TRY BEGIN CATCH -- no action needed in case of error END CATCH; SET @iterator = @iterator + 1 END SELECT * FROM dbo.#tables; --DROP TABLE IF EXISTS dbo.#tables;
| ranking | table_name | number_records |
| 1 | dbo.Date | 5844 |
| 2 | dbo.Geography | 305179 |
| 3 | dbo.HackneyLicense | 42958 |
| 4 | dbo.Time | 86400 |
| 5 | dbo.Weather | 526330 |
| 6 | dbo.Trip | 2838927 |
| 7 | dbo.Medallion | 13668 |
Comments:
1) It's a lot of code for a simple task, though the code can be easily duplicated and adapted for similar requirements. Unfortunately, it can lead in time also to many instances of the same code. When possible, one should consider maybe encapsulating the logic in a stored procedure.
2) It's usually a good idea to check how many records are available in the tables used for testing, as this can impact queries' performance and tables' appropriateness for the tests performed. Moreover, it's a good idea to understand the volume of data when taking over or working with a database.
3) If one removes the row_number function, the code should run also in SQL Server 2000. Similar solutions were used then for retrieving the record count.
4) Microsoft recommends not to drop the temporary tables explicitly, but let SQL Server handle this cleanup automatically and take thus advantage of the Optimistic Latching Algorithm, which helps prevent contention on TempDB [1]..
5) There are others who stumbled over this issue (see [1]).
6) The solution has been tested successfully also in SQL databases.
7) The whole code must be run together because the temporary table seems to have only a transitory scope! An attempt to rerun the last SELECT from #tables raises the error: "Invalid object name '#tables'"
Happy coding!
Previous Post <<||>> Next Post
References:
[1] Koen Verbeeck (2024) Get row counts of all tables in a Microsoft Fabric warehouse [link]
[2] Haripriya SB (2024) Do NOT drop #temp tables (link)
