How to fetch the row count for all tables in a SQL SERVER database

Question

I am searching for a SQL Script that can be used to determine if there is any data  i e  row count  in any of the tables of a given database    The idea is to re-incarnate the database in case there are any rows existing  in any of the database    The database being spoken of is Microsoft SQL SERVER   Could someone suggest a sample script

User · Answer

Don t use SELECT COUNT    FROM TABLENAME  since that is a resource intensive operation  One should use SQL Server Dynamic Management Views or System Catalogs to get the row count information for all tables in a database

User · Answer

This is my favorite solution for SQL 2008   which puts the results into a  TEST  temp table that I can use to sort and get the results that I need    SET NOCOUNT ON  DBCC UPDATEUSAGE 0   DROP TABLE  t  CREATE TABLE  t      name  NVARCHAR 128    rows  CHAR 11   reserved VARCHAR 18    data VARCHAR 18    index size VARCHAR 18   unused VARCHAR 18      INSERT  t EXEC sp msForEachTable  EXEC sp spaceused         SELECT   INTO TEST FROM  t  DROP TABLE  t  SELECT  name   rows   reserved  data  index size  unused FROM TEST   WHERE   rows   gt  0  AND  name LIKE  XXX

User · Answer

If you want to by pass the time and resources it takes to count    your 3million row tables   Try this per SQL SERVER Central by Kendal Van Dyke     Row Counts Using sysindexes  If you re using SQL 2000 you ll need to use sysindexes like so   -- Shows all user tables and row counts for the current database  -- Remove OBJECTPROPERTY function call to include system objects  SELECT o NAME    i rowcnt  FROM sysindexes AS i   INNER JOIN sysobjects AS o ON i id   o id  WHERE i indid  lt  2  AND OBJECTPROPERTY o id   IsMSShipped     0 ORDER BY o NAME   If you re using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice you should use the DMVs instead  like so   -- Shows all user tables and row counts for the current database  -- Remove is ms shipped   0 check to include system objects  -- i index id  lt  2 indicates clustered index  1  or hash table  0   SELECT o name    ddps row count  FROM sys indexes AS i   INNER JOIN sys objects AS o ON i OBJECT ID   o OBJECT ID   INNER JOIN sys dm db partition stats AS ddps ON i OBJECT ID   ddps OBJECT ID   AND i index id   ddps index id  WHERE i index id  lt  2  AND o is ms shipped   0 ORDER BY o NAME

User · Answer

Works on Azure  doesn t require stored procs   SELECT t name  s row count from sys tables t JOIN sys dm db partition stats s ON t object id   s object id AND t type desc    USER TABLE  AND t name not like   dss   AND s index id IN  0 1    Credit

User · Answer

Short and sweet  sp MSForEachTable  DECLARE  t AS VARCHAR MAX    SELECT  t   CAST COUNT 1  as VARCHAR MAX      CHAR 9    CHAR 9          FROM     PRINT  t    Output

User · Answer

The following SQL will get you the row count of all tables in a database   CREATE TABLE  counts       table name varchar 255       row count int    EXEC sp MSForEachTable  command1  INSERT  counts  table name  row count  SELECT        COUNT    FROM    SELECT table name  row count FROM  counts ORDER BY table name  row count DESC DROP TABLE  counts   The output will be a list of tables and their row counts   If you just want the total row count across the whole database  appending   SELECT SUM row count  AS total row count FROM  counts   will get you a single value for the total number of rows in the whole database

User · Answer

SELECT           SUM sdmvPTNS row count  AS  DBRows      FROM           sys objects AS sOBJ           INNER JOIN sys dm db partition stats AS sdmvPTNS                 ON sOBJ object id   sdmvPTNS object id     WHERE            sOBJ type    U            AND sOBJ is ms shipped   0           AND sdmvPTNS index id  lt  2     GO

User · Answer

I would make a minor change to Frederik s solution   I would use the sp spaceused system stored procedure which will also include data and index sizes    declare c tables cursor fast forward for  select table name from information schema tables   open c tables  declare  tablename varchar 255   declare  stmt nvarchar 2000   declare  rowcount int  fetch next from c tables into  tablename   while   fetch status   0  begin       select  stmt    sp spaceused      tablename       exec sp executesql  stmt      fetch next from c tables into  tablename   end   close c tables  deallocate c tables

User · Answer

Here s a dynamic SQL approach that also gives you the schema as well   DECLARE  sql nvarchar MAX   SELECT      sql   COALESCE  sql     UNION ALL                   SELECT                   s name       AS   Schema                      t name       AS   Table                COUNT    AS Count             FROM     QUOTENAME s name          QUOTENAME t name      FROM sys schemas s     INNER JOIN sys tables t ON t schema id   s schema id     ORDER BY         s name          t name  EXEC  sql    If needed  it would be trivial to extend this to run over all databases in the instance  join to sys databases

User · Answer

select all rows from the information schema tables view  and issue a count    statement for each entry that has been returned from that view   declare c tables cursor fast forward for select table name from information schema tables  open c tables declare  tablename varchar 255  declare  stmt nvarchar 2000  declare  rowcount int fetch next from c tables into  tablename  while   fetch status   0 begin      select  stmt    select  rowcount   count    from      tablename      exec sp executesql  stmt  N  rowcount int output    rowcount  rowcount OUTPUT      print N table       tablename     has     convert nvarchar 1000   rowcount      rows       fetch next from c tables into  tablename  end  close c tables deallocate c tables

User · Answer

SELECT      sc name       ta name TableName  SUM pa rows  RowCnt FROM      sys tables ta INNER JOIN sys partitions pa     ON pa OBJECT ID   ta OBJECT ID INNER JOIN sys schemas sc     ON ta schema id   sc schema id WHERE ta is ms shipped   0 AND pa index id IN  1 0  GROUP BY sc name ta name ORDER BY SUM pa rows  DESC

User · Answer

SQL Server 2005 or later gives quite a nice report showing table sizes - including row counts etc  It s in Standard Reports - and it is Disc Usage by Table   Programmatically  there s a nice solution at  http   www sqlservercentral com articles T-SQL 67624

User · Answer

This one looks better than the others I think   USE   enter your db name here  GO  SELECT      SCHEMA NAME A schema id                  --A Name  SUM B rows  AS  RowCount   Use AVG instead of SUM           A Name  AVG B rows  AS  RowCount  FROM        sys objects A INNER JOIN sys partitions B ON A object id   B object id WHERE       A type    U  GROUP BY    A schema id  A Name GO

[sql] How to fetch the row count for all tables in a SQL SERVER database

Examples related to sql

Examples related to sql-server

Examples related to rowcount