Interview Question - How to get the count of rows for each table of the particular database ? How.....

This is simple yet important question which may feature when someone is interviewing for SQL developer position.  

The question goes like this 
"How to get the count of rows for each table of the particular database ? How many ways you can think of to fetch the details ?"

Again second part of the question made it interesting because now interviewer wants to understand your knowledge about different ways of Looping in SQL server ?

One obvious answer for this question would be using CURSOR but I'll leave that to you to write instead I'll try to use in-build looping mechanism that comes handy in this situation.


Using SP_MsforeachTable :

sp_msforeachtable - It is in-built procedure which provides cursor like functionality. The SQL code enclosed within this procedure will execute for each table from the database. 

As you can see in the screenshot below

In the TEST database, I have 3 tables namely T1 (968 rows), T2 (123 rows) and T3 (123 rows). 

Now, I'll try to run the following query

EXEC TestCount..sp_MSforeachtable ' SELECT COUNT(1) As RecCount FROM ? '

As you would have noticed I have used " ? " (question mark) instead of table name in the SELECT statement. Internally sp_msforeachtable replaces this "?" by the table name one by one and executes the SELECT statement.

I received following result. In it each row represents the count of rows from some table but we don't know which row belongs to which table 

Hence we must tweak the query to display table name as well. And as I mentioned before " ? " represents the table name here so its just the matter of using it appropriately

EXEC TestCount..sp_MSforeachtable ' SELECT ''?'' as TableName,COUNT(1) As RecCount FROM ? '

And this gives us the following result

There is one procedure (sp_spaceused) which gives the complete storage related information about the object. 

Let us write one more query with sp_msforeachtable to get the detailed information then

EXEC sp_MSforeachtable ' exec sp_spaceused ''?'' '

and it produces the following output 

Using While Loop :

DECLARE @TAB TABLE

(

ID INT IDENTITY(1,1),

TABNAME VARCHAR(1000)

)

INSERT INTO @TAB (TABNAME)

SELECT name FROM SYS.tables

DECLARE @LOOP INT = 1,

@MAXCNT INT,

@NAME VARCHAR(1000),

@QUERY VARCHAR(4000)

SELECT @MAXCNT = MAX(ID) FROM @TAB

WHILE @LOOP <= @MAXCNT

BEGIN

SELECT @NAME = TABNAME FROM @TAB WHERE ID  = @LOOP

SET @QUERY = ' SELECT '''+ @NAME + ''' AS TABNAME, COUNT(1) AS CNT FROM ' + @NAME

--PRINT @QUERY

EXEC (@QUERY)

SET @LOOP = @LOOP + 1

END

To summarize at this moment I can think of 3 ways to get the required information

• sp_MSforeachtable
• While Loop
• Cursor

Please do let me know if you know any other way to extract this information