Post reply

Total Data Rows Data

  • August 19, 2011 at 1:45 am

    #243534

    Comments posted to this topic are about the item Total Data Rows Data

    The Fastest Methods aren't always the Quickest Methods

  • August 19, 2011 at 8:27 am

    #1370767

    Slightly easier: -

    DECLARE @SQL AS VARCHAR(MAX)

    SELECT @SQL = 'SELECT SUM(rn) FROM (' +

    ISNULL(STUFF((SELECT ' UNION ALL SELECT COUNT(*) AS rn FROM ' + table_catalog+'.'+table_schema+'.'+table_name

    FROM INFORMATION_SCHEMA.TABLES

    WHERE table_catalog=db_name() FOR XML PATH('')),1,10,''),'SELECT 0 AS rn') + ')a'

    EXEC(@SQL)

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • August 21, 2011 at 12:39 pm

    #1371190

    didn't think of that, I kinda had the script from here[/url] and then got the idea for get count of rows. Didn't really put as much thought into it as I should of 😀

    The Fastest Methods aren't always the Quickest Methods

  • August 24, 2011 at 6:41 am

    #1372508

    The downside to the methods described is that they require a query to be run on every table. The downside to my method below is that it uses the sys.sysindexes table, which may not be available in future versions of SQL Server. However, it uses the statistics on the indexes to find the row counts rather than having to query every table.

    SELECTCAST(o.name AS VARCHAR(50))AS TableName,

    i.rowsAS TableRows

    FROMsys.sysobjects o

    JOINsys.sysindexes i

    ONi.id = o.id

    WHEREi.indid IN (0, 1)

    ANDo.type = 'U'

    ANDo.name NOT IN ('dtproperties', 'sysdiagrams')

    ORDER BY o.name

    COMPUTESUM(i.rows)

  • May 12, 2016 at 7:19 am

    #1878592

    Thanks for the script.

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

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