Insert dynamic results into table

  • Hi All,

    I would like to insert the 2nd statement to a table. If I copy and paste to SSMS and run it is working.

    Is it possible to automatically run this, without copy and paste results into SSMS?

    create table #tbl_sysobjects_row_count (name sysname,row_count int)

    select 'insert into #tbl_sysobjects_row_count select '''+name+''', count(*) [row_count] from ['+name+']' from sys.objects where type ='u'

    --insert into #tbl_sysobjects_row_count select 'tbl_123', count(*) [row_count] from [tbl_123]

    select * from #tbl_sysobjects_row_count
  • So all you want to do is copy the table name and the number of records into a table? Why not query the  system tables? Sort of like this:

    SELECT, ao.object_id, p.rows
    FROM sys.all_objects ao
    INNER JOIN sys.partitions p
    ON ao.object_id = p.object_id
    WHERE ao.type_desc LIKE 'USER_TABLE'
    AND ao.is_ms_shipped = 0
    AND != 'sysdiagrams';
  • Thanks for the update. I am just trying to compare replication tables row count from publisher to subscriber. Thought of dynamically generate rowcount for all replicated tables both side and compare it from stored proc.

    I have tried this but it is not working correctly.


  • You could use sp_MSforeachtable to avoid writing a loop, but it will only work in the database it's run from, so you couldn't query two databases at the same time to compare them.

    EXEC sp_MSforeachtable 'INSERT #tbl_sysobjects_row_count SELECT ''?'' , Count(*)  FROM ?'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply