locked
Cursor to count records RRS feed

  • Question

  • Hi,

    I have a table like the below

    CREATE TABLE [dbo].[myScores](
    [ID] [int] NULL,
    [vw_name] [varchar](258) NULL,
    [score_table_name] [varchar](250) NULL,
    [Active] [int] NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[myScores] ([ID], [vw_name], [score_table_name], [Active]) VALUES (5, N'myview1', N'scoretable1', 1)
    INSERT [dbo].[myScores] ([ID], [vw_name], [score_table_name], [Active]) VALUES (9, N'myview2', N'scoretable2', 1)
    INSERT [dbo].[myScores] ([ID], [vw_name], [score_table_name], [Active]) VALUES (13, N'myview3', N'scoretable3', 1)
    INSERT [dbo].[myScores] ([ID], [vw_name], [score_table_name], [Active]) VALUES (2, N'myview4', N'scoretable4', 1)
    GO

    Now, the 2 columns 'vw_name' and 'score_table_name' each represent table names.

    I'd like to count the records in each of these 'column' tables. Thus, for example, have a result like the below, where 'view count' and 'score count' are the number of records in these tables.

    See below

    Please assist, 

    Thanks,

    


    • Edited by RonTech1 Wednesday, August 12, 2020 9:54 AM
    Wednesday, August 12, 2020 9:52 AM

Answers

  • Create a table with the columns you want (3 columns) and instead of doing SELECT in the cursor loop, you insert into your table and after the cursor you do a SELECT from that table.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:27 PM
    Wednesday, August 12, 2020 11:13 AM
  • Hi!!

    here is a 'rough' idea ...

    declare @sql nvarchar(max)
    declare @t1 varchar(max)
    declare @t2 varchar(max)
    
    declare @paramdefinition nvarchar(500)
    declare @counter_table1 INT
    declare @counter_table2 INT
    
    declare ccursor CURSOR FOR
    SELECT vw_name, score_table_name FROM myScores
    
    OPEN ccursor
    
    FETCH NEXT FROM ccursor
    INTO @t1, @t2
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        set @sql = 'select @ct1 = count(*) from ' + @t1
        set @paramdefinition = N'@ct1 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct1 = @counter_table1 OUTPUT
    
        set @sql = 'select @ct2 = count(*) from ' + @t2
        set @paramdefinition = N'@ct2 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct2 = @counter_table2 OUTPUT
    
        select @counter_table1, @counter_table2
    
        FETCH NEXT FROM ccursor
        INTO @t1, @t2
    END
    
    CLOSE ccursor
    DEALLOCATE ccursor

    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:28 PM
    Wednesday, August 12, 2020 10:39 AM
  • If cursor is not mandatory and the table is not huge, then consider an alternative:

    declare @s varchar(max)
    
    select @s = string_agg(s, ' union all ')
    from
    (
    	select concat('select ', ID, ' as ID, (select count(*) from ', QUOTENAME(vw_name), ') as [view count], (select count(*) from ', QUOTENAME(score_table_name), ') as [score count]') as s
    	from myScores
    ) t
    
    exec (@s)



    • Edited by Viorel_MVP Wednesday, August 12, 2020 11:02 AM
    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:27 PM
    Wednesday, August 12, 2020 11:01 AM

All replies

  • Hi!!

    here is a 'rough' idea ...

    declare @sql nvarchar(max)
    declare @t1 varchar(max)
    declare @t2 varchar(max)
    
    declare @paramdefinition nvarchar(500)
    declare @counter_table1 INT
    declare @counter_table2 INT
    
    declare ccursor CURSOR FOR
    SELECT vw_name, score_table_name FROM myScores
    
    OPEN ccursor
    
    FETCH NEXT FROM ccursor
    INTO @t1, @t2
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        set @sql = 'select @ct1 = count(*) from ' + @t1
        set @paramdefinition = N'@ct1 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct1 = @counter_table1 OUTPUT
    
        set @sql = 'select @ct2 = count(*) from ' + @t2
        set @paramdefinition = N'@ct2 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct2 = @counter_table2 OUTPUT
    
        select @counter_table1, @counter_table2
    
        FETCH NEXT FROM ccursor
        INTO @t1, @t2
    END
    
    CLOSE ccursor
    DEALLOCATE ccursor

    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:28 PM
    Wednesday, August 12, 2020 10:39 AM
  • Hi Emiliano,

    Thanks for the above. How can I modify to include the ID column.

    Thus have 3 columns in the output - [ID], [view count], [table count]

    Please advise,

    Thanks,

    Wednesday, August 12, 2020 10:51 AM
  • Hi 

    Sorry, and also have the results in a single result-set.

    Thanks

    Wednesday, August 12, 2020 10:55 AM
  • If cursor is not mandatory and the table is not huge, then consider an alternative:

    declare @s varchar(max)
    
    select @s = string_agg(s, ' union all ')
    from
    (
    	select concat('select ', ID, ' as ID, (select count(*) from ', QUOTENAME(vw_name), ') as [view count], (select count(*) from ', QUOTENAME(score_table_name), ') as [score count]') as s
    	from myScores
    ) t
    
    exec (@s)



    • Edited by Viorel_MVP Wednesday, August 12, 2020 11:02 AM
    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:27 PM
    Wednesday, August 12, 2020 11:01 AM
  • Hi,

    I am required to use a cursor in this instance.

    Thanks,

    Wednesday, August 12, 2020 11:11 AM
  • Create a table with the columns you want (3 columns) and instead of doing SELECT in the cursor loop, you insert into your table and after the cursor you do a SELECT from that table.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    • Marked as answer by RonTech1 Wednesday, August 12, 2020 12:27 PM
    Wednesday, August 12, 2020 11:13 AM
  • Here it is:

    declare @sql nvarchar(max)
    declare @id int
    declare @t1 varchar(max)
    declare @t2 varchar(max)
    
    declare @paramdefinition nvarchar(500)
    declare @counter_table1 INT
    declare @counter_table2 INT
    
    declare ccursor CURSOR FOR
    SELECT ID, vw_name, score_table_name FROM myScores
    
    OPEN ccursor
    
    FETCH NEXT FROM ccursor
    INTO @id, @t1, @t2
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        set @sql = 'select @ct1 = count(*) from ' + @t1
        set @paramdefinition = N'@ct1 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct1 = @counter_table1 OUTPUT
    
        set @sql = 'select @ct2 = count(*) from ' + @t2
        set @paramdefinition = N'@ct2 int OUTPUT'
        exec sp_executesql @sql, @paramdefinition, @ct2 = @counter_table2 OUTPUT
    
        insert into myScoresCount values (@id, @counter_table1, @counter_table2)
    
        FETCH NEXT FROM ccursor
        INTO @id, @t1, @t2
    END
    
    CLOSE ccursor
    DEALLOCATE ccursor

    considering a result table as:

    CREATE TABLE [dbo].[myScoresCount](
    [ID] [int] NULL,
    [view_count] [int] NULL,
    [score_count] [int] NULL
    ) ON [PRIMARY]
    GO

    Wednesday, August 12, 2020 12:13 PM
  • SELECT S.ID, p1.rows, p2.rows
    FROM   myScores S
    JOIN   sys.partitions p1 ON object_id(S.vw_name) = p1.object_id
    JOIN   sys.partitions p2 ON object_id(S.score_table_name) = p2.object_id

    This assumes that the tables really are tables. It will not work if they are views as the name suggests.

    Also, I'm making the assumptions that the tables are not partitioned.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 12, 2020 10:00 PM