Post reply

SQL tables and rows counts PIVOT

  • May 26, 2015 at 2:36 pm

    #298613

    Can anyone share if you have a script for finding row counts of two specific tables in each database of SQL in PIVOT format . e.g

    Database || TableName1| Tablename2|| RowsCounts

  • May 27, 2015 at 10:03 pm

    #1801137

    why not just write all the results to a temporary table and then just use one of the pivot table examples here on SSC? Look them up in the Articles section.

    If you want to store the data permanently, just write it to a permanent table somewhere.

  • May 29, 2015 at 7:31 am

    #1801576

    I think the larger problem is just getting a list of stats from systables from each DB.

    Do this

    create table tempdb.dbo.MyStats( DBname sysname,tablename sysname,rows bigint)

    EXEC sp_MSforeachdb 'USE ? insert into tempdb.dbo.MyStats ( DBName, tablename,rows) SELECT db_name(db_id()), object_name(id),rowcnt from sysindexes where indid <2'

    select * from tempdb.dbo.MyStats

    Then you will have your stats.

    Add your tablenames as a filter on the sysindexes.

    where indid <2 and 'object_name(id) in (''mytable1'',''mytable2'')

    Once you have your table, then my next question is,

    Your example has columns DBNAME|TABLENAME1|TABLENAME2|ROWCOUNT

    Which rowcount do you want, or should it be

    DBNAME|TABLENAME1|TABLENAME2|ROWCOUNT1|ROWCOUNT2

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

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

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