{"id":585,"date":"2014-10-09T19:43:22","date_gmt":"2014-10-09T13:43:22","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=585"},"modified":"2014-10-17T13:50:03","modified_gmt":"2014-10-17T07:50:03","slug":"data-sizing-or-sql-server-rowcount-without-doing-a-table-scan","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/data-sizing-or-sql-server-rowcount-without-doing-a-table-scan\/","title":{"rendered":"DATA sizing or SQL Server Rowcount without doing a table scan"},"content":{"rendered":"

\n\tScenario Proposal<\/strong>
\n<\/h3>\n

\n\tWhile working on different database projects I usually find it very necessary to perform a data sizing exercise on all tables focusing mostly on the number of rows in order to determine a growth factor of each table. This kind of information is useful in performing long term planning of growth and scalability of the data that we are managing.\n<\/p>\n

\n\tOut of an exercise like this one we can also determine future needs for data archiving.\n<\/p>\n

\n\t\n<\/p>\n

\n\tSolution<\/strong>
\n<\/h3>\n

\n\tThe most trivial way of achieving this task would be perform an “Select Count(1) from” <\/strong>each of the tables of our database but this could create a lot of overhead especially for large databases and large tables.\n<\/p>\n

\n\tThe optimal way to achieve this is by using the sp_spaceused system stored procedure.\n<\/p>\n

\n\tSample code below to outline the usage:<\/strong>
\n<\/h3>\n

\n\t— variable declaration  — www.dotnetsql.info\n<\/p>\n

\n\t— declare @n to store the total number of tables\n<\/p>\n

\n\tdeclare @n int\n<\/p>\n

\n\t— declare @i to use it as an index\n<\/p>\n

\n\tdeclare @i int\n<\/p>\n

\n\t— declare @tn to retrieve the tablename on the @i position\n<\/p>\n

\n\tdeclare @tn varchar(1000)\n<\/p>\n

\n\tset         @i = 1\n<\/p>\n

\n\t— table to store all the tablenames in scope\n<\/p>\n

\n\tdeclare @t table(\n<\/p>\n

\n\tid int identity(1,1),\n<\/p>\n

\n\tname varchar(1000)\n<\/p>\n

\n\t)\n<\/p>\n

\n\t— table to store the return results\n<\/p>\n

\n\tdeclare @tempt table\n<\/p>\n

\n\t(  \n<\/p>\n

\n\ttableName varchar(100),\n<\/p>\n

\n\tnumberofRows varchar(100),\n<\/p>\n

\n\treservedSize varchar(50),\n<\/p>\n

\n\tdataSize varchar(50),\n<\/p>\n

\n\tindexSize varchar(50),\n<\/p>\n

\n\tunusedSize varchar(50)\n<\/p>\n

\n\t)\n<\/p>\n

\n\t— code  —- www.dotnetsql.info\n<\/p>\n

\n\t— insert all the tables in the database in a temporary table\n<\/p>\n

\n\tinsert into @t\n<\/p>\n

\n\t                        (name)\n<\/p>\n

\n\tselect                  s.name+'.'+t.name\n<\/p>\n

\n\tfrom  sys.tables  t\n<\/p>\n

\n\tjoin  sys.schemas s     on s.schema_id = t.schema_id\n<\/p>\n

\n\t— get the rowcount of the temporary table\n<\/p>\n

\n\tset @n = @@ROWCOUNT+1\n<\/p>\n

\n\t— loop through the table to retrieve table names\n<\/p>\n

\n\twhile (@i<@n)           begin\n<\/p>\n

\n\t— get the current table name\n<\/p>\n

\n\t      select @tn = name\n<\/p>\n

\n\t      from  @t\n<\/p>\n

\n\t      where id = @i\n<\/p>\n

\n\t— insert the result of sp_spaceused into the results table\n<\/p>\n

\n\t      insert into @tempt\n<\/p>\n

\n\t      EXEC sp_spaceused @tn\n<\/p>\n

\n\t— increment the current row\n<\/p>\n

\n\t      set @i=@i+1\n<\/p>\n

\n\tend\n<\/p>\n

\n\t— return the rowcount and data usage\n<\/p>\n

\n\tselect *\n<\/p>\n

\n\tfrom @tempt\n<\/p>\n

\n\tThanks for reading this article,<\/strong>\n<\/p>\n

\n\tNext steps :<\/strong>\n<\/p>\n

    \n
  1. \n

    \n\t\t\tAdd this script to your database toolkit<\/strong>\n\t\t<\/p>\n<\/li>\n

  2. \n

    \n\t\t\tShare this with your colleagues because Sharing is Learning<\/strong>\n\t\t<\/p>\n<\/li>\n

  3. \n

    \n\t\t\tComment below if you need any assistance<\/strong>\n\t\t<\/p>\n<\/li>\n<\/ol>\n

    \n\tPowered by CodeReview – Let's make it Better!<\/a><\/strong><\/em><\/p>\n

    Share this:<\/h3>