Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2748
  • Last Modified:

how can i loop through the tables in my database and do a record count for each table?

can someone tell me how i can loop through each table in my database and return a record count for each table?

thanks!
0
trevoray
Asked:
trevoray
  • 14
  • 9
  • 5
  • +3
1 Solution
 
danblakeCommented:
Something like this should do the trick:
select table_name into #LoopInput from INFORMATION_SCHEMA.TABLES


declare @table_name sysname
select top 1 @table_name=table_name from #LoopInput order by table_name
print @table_name

while exists (select top 1 table_name from #LoopInput)
begin
      exec ('select count(*) as '+@table_name+' from ['+@table_name+']')
   delete from #LoopInput
   where table_name = @table_name
end

Alternativly you can always place the exec results into a table, and have a second field indicating the table_name, such as: (next post).
0
 
arbertCommented:
If you just want  a quick count....

sp_msforeachtable 'select count(*) from ?'
0
 
danblakeCommented:
Try this one, better implementation:
create table results
(counter_val int,
 table_name sysname)
go

select table_name into #LoopInput from INFORMATION_SCHEMA.TABLES

declare @table_name sysname
select top 1 @table_name=table_name from #LoopInput order by table_name
print @table_name

while exists (select top 1 table_name from #LoopInput)
begin
   insert into results
      exec ('select count(*) as counter_val, '''+@table_name+''' as table_name
             from ['+@table_name+']')
   
      delete from #LoopInput
   where table_name = @table_name
end

select * from results
/*This has stored the final output as rqd*/
/*All data is stored in two columns 1: counter_val -- counter value / row-count, 2nd column: table_name storing the name of the table in question.*/
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
arbertCommented:
Actually, this is a little better:

sp_msforeachtable 'select ''?'' as tablename,count(*) from ?'


0
 
arbertCommented:
Notice those are double single quotes around the first ?
0
 
HilaireCommented:
also consider using
exec sp_MSForEachTable 'EXEC sp_spaceused @objname=''?'' '

0
 
trevorayAuthor Commented:
ok danblake, i cannot create any new tables, i don't have permissions for that. i just want to be able to view the results in query anaylizer. this is going to be a one time operation, so i'm not really worried that much about efficiency. can you tell me how the best way knowing this? thanks
0
 
trevorayAuthor Commented:
i'd rather not use a stored procedure
0
 
arbertCommented:
Like I said above--without creating new tables, in query analyzer run sp_msforeachtable 'select ''?'' as tablename,count(*) from ?'
0
 
danblakeCommented:
You're going to have to create a cursor .. (nasty..)

but here it is.. (WIP)..
0
 
trevorayAuthor Commented:
if i did run the sp_msforeachtable sp, what would i put in place of the '?'   ?
0
 
danblakeCommented:

/*
**  Cursor method to cycle through the Information Schema Tables view and get Rowcount for each table
**
** Revision History:
** ---------------------------------------------------
**  Date       Name       Description      Project
** ---------------------------------------------------
**  13/04/04   DJB        As given above     Tools
**
*/

SET NOCOUNT ON

 
-- declare all variables!
DECLARE  @TableName     sysname,
 

-- declare the cursor

DECLARE ISTTabs CURSOR FOR

SELECT    table_name

FROM      INFORMATION_SCHEMA.TABLES

 

OPEN ISTTabs
 
FETCH ISTTabs INTO @TableName
 

-- start the main processing loop.

WHILE @@Fetch_Status = 0

   BEGIN

   -- This is where you perform your detailed row-by-row

   -- processing.

     exec ('select count(*) as counter_val, '''+@table_name+''' as table_name
             from ['+@table_name+']')

   -- Get the next row.

   FETCH ISTTabs INTO @TableName
   END

CLOSE ISTTabs

DEALLOCATE ISTTabs





0
 
HilaireCommented:
>>what would i put in place of the '?'  <<
Leave the  '?' as is, it is substituted with the actual table_name inside the loop
0
 
danblakeCommented:
Few bugs have now been removed...
/*
**  Cursor method to cycle through the Information Schema Tables view and get Rowcount for each table
**
** Revision History:
** ---------------------------------------------------
**  Date       Name       Description      Project
** ---------------------------------------------------
**  13/04/04   DJB        As given above     Tools
**
*/

SET NOCOUNT ON

 
-- declare all variables!
DECLARE  @Table_Name     sysname
 

-- declare the cursor

DECLARE ISTTabs CURSOR FOR

SELECT    table_name

FROM      INFORMATION_SCHEMA.TABLES

 

OPEN ISTTabs
 
FETCH ISTTabs INTO @Table_Name
 

-- start the main processing loop.

WHILE @@Fetch_Status = 0

   BEGIN

   -- This is where you perform your detailed row-by-row

   -- processing.

     exec ('select count(*) as counter_val, '''+@table_name+''' as table_name
             from ['+@table_name+']')

   -- Get the next row.

   FETCH ISTTabs INTO @Table_Name
   END

CLOSE ISTTabs

DEALLOCATE ISTTabs
0
 
Scott PletcherSenior DBACommented:
I found myself doing that so often when working with unfamiliar dbs that I wrote a SP to assist.  The SPs (including a subrtn SP), with comments and help info, is after the sample invocations of the SP below.  

I know this seems like overkill but please try it with various inputs.  It is very flexible and I find I miss it greatly when I work on dbs where I haven't installed it.

If you find any bugs, please let me know:  scott.pletcher@ipaper.com


EXEC sp_zCount '?'  -- list help for the SP (only, no tbl counts)
EXEC sp_zCount  -- list all tbls using row counts in sysindexes (not necessarily 100% accurate but very fast)
EXEC sp_zCount @method = 'COUNT' -- list all tables using COUNT(*)
EXEC sp_zCount 'ALL', 'COUNT'  -- same as prev. cmd
EXEC sp_zCount 'd* NOT dt*'  -- list all tbls beginning with d except those beginning with dt
EXEC sp_zCount 'LIKE [a-f]' -- list all tbls beginning with a thru f


Naturally if you prefer, you can change the default in sp_zCount to be COUNT rather than FAST.


/* Code to create the subproc SP and sp_zCount SP and make them available from every db */


USE master
GO
CREATE PROCEDURE sp_zGen_Conditions
      @operand VARCHAR(50),
      @list VARCHAR(5000),
      @where VARCHAR(8000) = '' OUTPUT,
      @wildcard VARCHAR(4) = '%*',
      @debug TINYINT = 0
AS
-- Author: Scott Pletcher.  International Paper, Inc.  May be freely distributed if this line is left in place.

IF @debug > 0
      PRINT 'sp_zGen_Conditions Entry'
DECLARE @end TINYINT
DECLARE @name VARCHAR(60)
DECLARE @firstInSwi BIT
DECLARE @firstLikeSwi BIT
DECLARE @firstNotInSwi BIT
DECLARE @firstNotLikeSwi BIT
DECLARE @notInd TINYINT
DECLARE @likeInd TINYINT
DECLARE @delimIsQuote BIT
DECLARE @whereIn VARCHAR(2000)
DECLARE @whereLike VARCHAR(2000)
DECLARE @whereNotIn VARCHAR(2000)
DECLARE @whereNotLike VARCHAR(2000)

WHILE LEN(@wildcard) > 1
BEGIN
      SET @list = REPLACE(@list, SUBSTRING(@wildcard, 2, 1), LEFT(@wildcard, 1))  --replace secondary wildcard char with primary wildcard char
      SET @wildcard = STUFF(@wildcard, 2, 1, '')  --remove just-replaced wildcard char
END
SET @whereIn = ''
SET @whereLike = ''
SET @whereNotIn = ''
SET @whereNotLike = ''
SET @firstInSwi = 1
SET @firstLikeSwi = 1
SET @firstNotInSwi = 1
SET @firstNotLikeSwi = 1
SET @notInd = 0
SET @likeInd = 0
SET @whereIn = ''
SET @whereLike = ''
SET @whereNotIn = ''
SET @whereNotLike = ''
SET @list = LTRIM(@list)
WHILE LEFT(@list, 1) <> ''
BEGIN
      SET @delimIsQuote = 0
      IF @debug > 0
            PRINT 'List = ' + @list
      IF LEFT(@list, 1) = ';'
      BEGIN
            SET @notInd = 0
            SET @likeInd = 0
            SET @list = LTRIM(SUBSTRING(@list, 2, LEN(@list) - 1))
      END --IF
      IF @likeInd = 1
            SET @likeInd = 0
      IF LEFT(@list, 4) = 'NOT '
      BEGIN
            SET @notInd = 2
            SET @likeInd = 0
            SET @list = SUBSTRING(@list, 5, LEN(@list) - 4)
      END --IF
      IF LEFT(@list, 5) = 'LIKE '
      BEGIN
            SET @likeInd = 2
            SET @list = SUBSTRING(@list, 6, LEN(@list) - 5)
      END --IF
      IF LEFT(@list, 1) = '['
      BEGIN
            IF @likeInd = 0
            BEGIN
                  SET @list = SUBSTRING(@list, 2, LEN(@list) - 1)
                  SET @end = CHARINDEX(']', @list)
            END --IF
            ELSE
            BEGIN
                  SET @end = CHARINDEX(']', @list, 2)      
                  IF @end > 0
                        IF SUBSTRING(@list, @end + 1, 1) NOT IN ('', ' ')
                              SET @end = CHARINDEX(' ', @list)
                        ELSE
                              SET @end = @end + 1
            END --ELSE
      END --IF
      ELSE
      IF LEFT(@list, 1) = '"'
      BEGIN
            SET @delimIsQuote = 1
            SET @list = SUBSTRING(@list, 2, LEN(@list) - 1)
            SET @end = CHARINDEX('"', @list)
      END --IF
      ELSE
      IF LEFT(@list, 1) = ''''
      BEGIN
            SET @delimIsQuote = 1
            SET @list = SUBSTRING(@list, 2, LEN(@list) - 1)
            SET @end = CHARINDEX('''', @list)
      END --IF
      ELSE
      BEGIN
            SET @end = CHARINDEX(',', @list)
            IF @end = 0 OR CHARINDEX(' ', @list) < @end
                  SET @end = CHARINDEX(' ', @list)
      END --ELSE
      IF @end > 0
      BEGIN
            SET @name = LEFT(@list, @end - 1)
            IF LEFT(@name, 1) <> '[' OR @delimIsQuote = 1
                  SET @end = @end + 1
            SET @list = SUBSTRING(@list, @end, LEN(@list))
            IF LEFT(LTRIM(@list), 1) = ','
                  SET @list = SUBSTRING(LTRIM(@list), 2, LEN(@list) - 1)
      END --IF
      ELSE
      BEGIN
            SET @name = LTRIM(@list)
            SET @list = NULL
      END --ELSE
      IF @debug > 0
            PRINT 'Next entry = /' + @name + '/'
      IF RIGHT(@name, 1) = ';'
      BEGIN
            SET @name = LEFT(@name, LEN(@name) - 1)
            SET @list = ';' + @list
      END --IF
      IF @likeInd < 2
            IF CHARINDEX(@wildcard, @name) > 0            
                  SET @likeInd = 1

      IF LEFT(@name, 1) <> ''''
            SET @name = '''' + @name
      IF @likeInd > 0 AND CHARINDEX(@wildcard, @name) = 0
            SET @name = @name + @wildcard
      IF SUBSTRING(@name, LEN(@name), 1) <> ''''
            SET @name = @name + ''''
      IF @notInd = 0
      BEGIN
            IF @likeInd > 0
            BEGIN
                  SET @whereLike = @whereLike +  
                        CASE WHEN @firstLikeSwi > 0 THEN '(' ELSE ' OR ' END +
                        @operand + ' LIKE ' + @name
                  SET @firstLikeSwi = 0
            END --IF
            ELSE
            BEGIN
                  SET @whereIn = @whereIn + CASE WHEN @firstInSwi > 0 THEN
                        '(' + @operand + ' IN (' ELSE ',' END + @name
                  SET @firstInSwi = 0
            END --ELSE
      END --IF
      ELSE
      BEGIN
            IF @likeInd > 0
            BEGIN
                  SET @whereNotLike = @whereNotLike +  
                        CASE WHEN @firstNotLikeSwi > 0 THEN '(' ELSE ' AND ' END +
                        @operand + ' NOT LIKE ' + @name
                  SET @firstNotLikeSwi = 0
            END --IF
            ELSE
            BEGIN
                  SET @whereNotIn = @whereNotIn + CASE WHEN @firstNotInSwi > 0 THEN
                        '(' + @operand + ' NOT IN (' ELSE ',' END + @name
                  SET @firstNotInSwi = 0
            END --ELSE
      END --ELSE
      SET @list = LTRIM(@list)
END --WHILE

skipTblsProcessing:

IF @firstInSwi = 0
      SET @whereIn = @whereIn + '))'
IF @firstLikeSwi = 0
      SET @whereLike = @whereLike + ')'
IF @firstNotInSwi = 0
      SET @whereNotIn = @whereNotIn + '))'
IF @firstNotLikeSwi = 0
      SET @whereNotLike = @whereNotLike + ')'
IF @debug > 0
BEGIN
      PRINT 'whereIn      = ' + @whereIn
      PRINT 'whereLike    = ' + @whereLike
      PRINT 'whereNotIn   = ' + @whereNotIn
      PRINT 'whereNotLike = ' + @whereNotLike
END
SET @where = CASE WHEN @whereIn <> '' OR @whereLike <> '' OR @whereNotIn <> '' OR
      @whereNotLike <> '' THEN 'AND ' ELSE '' END +
      CASE WHEN @whereIn <> '' AND @whereLike <> '' THEN '( ' ELSE '' END +
      @whereIn + ' ' +
      CASE WHEN @whereIn <> '' AND @whereLike <> '' THEN CHAR(13) + ' OR ' ELSE '' END +
      @whereLike + ' ' +
      CASE WHEN @whereIn <> '' AND @whereLike <> '' THEN ') ' ELSE '' END +
      CASE WHEN @whereNotIn <> '' AND (@whereIn <> '' OR @whereLike <> '') THEN
            CHAR(13) + 'AND ' ELSE '' END +
      @whereNotIn + ' ' +
      CASE WHEN @whereNotLike <> '' AND (@whereIn <> '' OR @whereLike <> '' OR @whereNotIn <> '') THEN
            CHAR(13) + 'AND ' ELSE '' END +
      @whereNotLike + ' '
IF @debug > 0
      PRINT 'sp_zGen_Conditions Exit'
GO
EXEC sp_MS_MarkSystemObject 'sp_zGen_Conditions'




USE master
GO
DROP PROCEDURE sp_zCount
GO
CREATE PROCEDURE sp_zCount
      @tbls VARCHAR(2000) = 'ALL', --list of specific and/or wildcard tables to process/not process
      @method VARCHAR(8) = 'FAST', --FAST=use rowcnt from sysindexes, not COUNT(*); COUNT/COUNT(*)=use COUNT(*), not rowcnt from sysindexes
      @orderBy VARCHAR(100) = '',  --sort column(s); may be "name" and/or "rows"|"rowcount", with ASC|DESC if desired
      @debug TINYINT = 0 --1+=print debugging info when executing
AS
-- Author: Scott Pletcher.  International Paper, Inc.  May be freely distributed if this line is left in place.

IF @tbls IN ('HELP', '/HELP', '/H') OR CHARINDEX('?', @tbls) > 0
      GOTO help
IF @method NOT IN ('FAST', 'COUNT', 'COUNT(*)')
BEGIN
      RAISERROR ('@Method (second parameter) must be FAST | COUNT | COUNT(*) .', 16, 1)
      RETURN -1  
END --IF

IF OBJECT_ID('tempdb..#tblInfo') IS NOT NULL
      DROP TABLE #tblInfo
CREATE TABLE #tblInfo (    
      name  VARCHAR(50) NOT NULL,
      rows  INT NOT NULL DEFAULT 0 )

SET NOCOUNT ON

DECLARE @debugSubrtns TINYINT
DECLARE @tblName VARCHAR(60)
DECLARE @tblWhere VARCHAR(8000)
DECLARE @sql VARCHAR(8000)
DECLARE @username VARCHAR(60)
DECLARE @tblNameMaxLen TINYINT
DECLARE @numTbls INT

SET @debugSubrtns = CASE WHEN @debug <= 0 THEN 0 ELSE @debug - 1 END

IF @tbls IS NULL
      SET @tbls = 'ALL'

IF @tbls = 'ALL'
BEGIN
      SET @tblWhere = ''
END --IF
ELSE
BEGIN
      EXEC sp_zGen_Conditions 'OBJECT_NAME(so.id)', @tbls, @tblWhere OUTPUT, @debug = @debugSubrtns
      IF @debug > 0
            PRINT @tblWhere
END --ELSE

IF @method = 'FAST'
BEGIN      
      SET @sql = 'INSERT INTO #tblInfo (name, rows) ' + CHAR(13) +
          'SELECT LEFT(OBJECT_NAME(si.id), 40) AS [Table Name], ' +
            --'RIGHT(SPACE(50) + LEFT(OBJECT_NAME(si.id), 50) + '' '', 50) AS ' +
            --'[                                       Table Name ], ' + CHAR(13) +
            'rowcnt AS [numRows] ' + CHAR(13) +
            'FROM sysindexes si WITH (NOLOCK) ' + CHAR(13) +
            'INNER JOIN sysobjects so WITH (NOLOCK) ON si.id = so.id ' + CHAR(13) +
            'WHERE si.indid < 2 AND so.xtype = ''U'' ' +            
            @tblWhere + CHAR(13) +
            'ORDER BY LEFT(OBJECT_NAME(si.id), 40) '
      IF @debug > 0
            PRINT @sql
      EXEC (@sql)
      SET @numTbls = @@ROWCOUNT
      GOTO displayTables
END --IF

--method is 'COUNT', so do COUNT(*) on each table to get exact row counts

SET @sql = 'DECLARE csrGetTableNames CURSOR GLOBAL STATIC FORWARD_ONLY FOR ' + CHAR(13) +
      'SELECT su.name, so.name ' + CHAR(13) +
      'FROM sysobjects so WITH (NOLOCK) ' + CHAR(13) +
      'LEFT OUTER JOIN sysusers su WITH (NOLOCK) ON so.uid = su.uid ' + CHAR(13) +
      'WHERE so.type = ''U'' ' + CHAR(13) +
      @tblWhere + CHAR(13) +
      'ORDER by so.name ' + CHAR(13) +
      'FOR READ ONLY '
IF @debug > 0
      PRINT @sql + CHAR(13)
EXEC (@sql)

OPEN csrGetTableNames

WHILE (10=10)
BEGIN
      FETCH NEXT FROM csrGetTableNames INTO @username, @tblName
      IF @@FETCH_STATUS <> 0
            BREAK
      SET @sql = 'INSERT INTO #tblInfo (name, rows) SELECT ' + CHAR(39) + @tblName +
            CHAR(39) + ', (SELECT COUNT(*) FROM [' + @username + '].' + '[' + @tblName + '])'
      IF @debug > 0
            PRINT @sql
      EXEC (@sql)
END --WHILE 10

CLOSE csrGetTableNames
DEALLOCATE csrGetTableNames


displayTables:
IF NOT EXISTS(SELECT 1 FROM #tblInfo)
BEGIN
      PRINT 'There are no matching User Tables in this database.'
      RETURN 0
END --IF
IF @method = 'FAST'
BEGIN
      PRINT 'NOTE: Row counts may only be approximate.  For absolute accuracy, '
      PRINT '      specify COUNT as @method (parameter 2).'
      PRINT ' '
END --IF
SET @tblNameMaxLen = (SELECT MAX(LEN(name)) FROM #tblInfo)
IF @tblNameMaxLen < 18
    SET @tblNameMaxLen = 18
SET @sql = 'SELECT CAST(name AS VARCHAR(' + CAST(@tblNameMaxLen AS VARCHAR(2)) + ')) ' +
    'AS [Table_Name], ' +
    'RIGHT(SPACE(9) + CAST(rows AS VARCHAR(9)), 9) ' +
    'AS [   # Rows] ' +
    'FROM #tblInfo ' +
      'UNION ALL ' +
      'SELECT '' '', '' ''' +
      'UNION ALL ' +
      'SELECT ''*Grand Total Rows*'', RIGHT(SPACE(9) + CAST(SUM(rows) AS VARCHAR(9)), 9) ' +
      'FROM #tblInfo ' +
      CASE WHEN @orderBy = '' THEN '' ELSE 'ORDER BY ' +
            REPLACE(REPLACE(REPLACE(@orderBy, 'rowcount', '[   # Rows]'), 'rows', '[   # Rows]'), 'name','[Table_Name]') + ' ' END
IF @debug > 0
    PRINT @sql
EXEC (@sql)
SET @numTbls = CASE WHEN @@ROWCOUNT = 0 THEN 0 ELSE @@ROWCOUNT - 2 END

PRINT 'Total tables listed = ' + CAST(@numTbls AS VARCHAR(6))
GOTO endOfProc

help:
PRINT 'sp_zCount accepts up to 4 parameters, as follows:'
PRINT '#1: List of table names.  Optional, Default = ALL. (List row counts for all tables).'
PRINT '    Names may be generic (use * or % as wildcard char or prefix with keyword LIKE).'
PRINT '    You may also exclude table(s) by specifying keyword NOT in front of names.'
PRINT '    Examples: "ab* e*" lists all tables beginning with ab or e.'
PRINT '        "NOT c%" lists all tables except those beginning with c.'
PRINT '        "c* d* NOT ca* de*; g* NOT gr*" lists all tables beginning with c, d, or g'
PRINT '            except those that begin with ca, de, or gr.'
PRINT '#2: Method to determine number of rows.  Optional, Default = FAST.'
PRINT '    May be FAST | COUNT | COUNT(*).  FAST uses row counts from sysindexes; '
PRINT '    COUNT/COUNT(*) issues an actual SELECT COUNT(*) against each table.'
PRINT '#3: Order by columns.  Optional, Default = NAME ASC.'
PRINT '    May be NAME [ASC|DESC] | ROWS [ASC|DESC] | ROWCOUNT [ASC|DESC]'
PRINT '    Examples: "rows DESC"; "rows DESC name".'
PRINT '#4: Controls display of debugging info.  Optional, Default = 0.'
PRINT '    0 = Don''t display debugging info, >0 = do display debugging info.'
endOfProc:
GO
EXEC sp_MS_MarkSystemObject 'sp_zCount'
0
 
trevorayAuthor Commented:
wow. i'm a bit overwhelmed right now. i seem to be out of my league here. i thought this would be something simple. silly me.
0
 
danblakeCommented:
Its really down to you to choose, depends on how stringent the criteria is:
1) Not use a stored procedure
2) Not use any temporary tables or global tables

I think we're all just having a bit of fun and showing off a little bit here..
0
 
Scott PletcherSenior DBACommented:
Btw, this command:

EXEC sp_zCount 'LIKE [a-f]' -- list all tbls beginning with a thru f

looks like it shouldn't work because there's no ending %.  And that's true, except that the subrtn adds a % to the end of the value if LIKE is used and an */% is not found in the string.  So, this will also work fine:

EXEC sp_zCount 'a b c'  -- same as specifying a% b% c%


But, if a */% is found, it does *not* add one; for example:

EXEC sp_zCount 'LIKE %ex'

will list table names ending in ex.  That's intentional; sometimes you want to go by the table suffix.
0
 
Scott PletcherSenior DBACommented:
I know, it seems overwhelming, but if you open QA and run the SPs creation code *once*, from then on you can use:

EXEC sp_zCount

in any db to get a quick count of rows in every table.
0
 
trevorayAuthor Commented:
ok. just found out i don't have permission to create sp.
0
 
Scott PletcherSenior DBACommented:
Ouch! Sorry :-(, used to dealing with db admins.  Unless you can get dba to create SPs for you, you will have to use a simpler method.
0
 
trevorayAuthor Commented:
ok. dan, i'm trying your latest now. should it take a while to run? it seems to be taking a while.
0
 
trevorayAuthor Commented:
it's at 2:35 dan and running

i'm getting nervous...
0
 
trevorayAuthor Commented:
4:10 ....
0
 
trevorayAuthor Commented:
ok, it's working. cool! thanks!
0
 
danblakeCommented:
dan, i'm trying your latest now. should it take a while to run? it seems to be taking a while.
(Northwind ran in < 2 seconds: Diff User DB 67 tables -- 36 seconds)
Depends on how many tables there are...

If you want the results quicker select scroll output as recieved option on in Query analyser (under tools -> options -> results).

0
 
Scott PletcherSenior DBACommented:
If you run it on db with tables that have millions of rows, it could take a *very long* time.  That is the point of the 'FAST' option in the SP.  You can simulate that like so:

SELECT LEFT(OBJECT_NAME(si.id), 40) AS [Table Name],
      rowcnt AS [Estimated Number of Rows]
FROM sysindexes si WITH (NOLOCK)
INNER JOIN sysobjects so WITH (NOLOCK) ON si.id = so.id
WHERE si.indid < 2 AND so.xtype = 'U'
ORDER BY LEFT(OBJECT_NAME(si.id), 40)

This may not be 100% accurate, but is likely to be close enough for most purposes.  If you need an absolutely accurate total of every table, you will have to use COUNT(*).
0
 
trevorayAuthor Commented:
oh. it's going through all the views too
0
 
danblakeCommented:
Yes, there is only one mod required to fix this,
add:
where table_type = 'BASE TABLE'

As indicated here:
-- declare the cursor
DECLARE ISTTabs CURSOR FOR
SELECT    table_name
FROM      INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE'

/*This should eliminate your view problem..*/
0
 
trevorayAuthor Commented:
dan,

i got all these errors. can you tell me how to avoid these errors AND not count the views?
0
 
trevorayAuthor Commented:
oh ok, i see your above post on how to elimnate views, how can i eliminate the errors?
0
 
mbwatkinsCommented:
Mind if I jump in?  If you're looking for "down and dirty", run the following in a query window:

select 'select count(*) as ''' + name + ''' from ' + name
from sysobjects
where type = 'u'

Paste the results in a new window and run.  Again, just a down-and-dirty-use-it-once type of solution.  (This is my first post, so be gentle!)

Good Luck
0
 
danblakeCommented:
i got all these errors, what was the error ?
 -- I had no errors here on Nwind/Trigg..


/*
**  Cursor method to cycle through the Information Schema Tables view and get Rowcount for each table
**
** Revision History:
** ---------------------------------------------------
**  Date       Name       Description      Project
** ---------------------------------------------------
**  13/04/04   DJB        As given above     Tools
** v2 /*removing bug that was in the first post of an extra , */ and v3 limit to tables only.
*/

SET NOCOUNT ON

 
-- declare all variables!
DECLARE  @Table_Name     sysname
 

-- declare the cursor

DECLARE ISTTabs CURSOR FOR
SELECT    table_name
FROM      INFORMATION_SCHEMA.TABLES
where table_type = 'BASE TABLE'
 

OPEN ISTTabs
FETCH ISTTabs INTO @Table_Name
 

-- start the main processing loop.

WHILE @@Fetch_Status = 0
   BEGIN

   -- This is where you perform your detailed row-by-row
   -- processing.

     exec ('select count(*) as counter_val, '''+@table_name+''' as table_name
             from ['+@table_name+']')

   -- Get the next row.
   FETCH ISTTabs INTO @Table_Name
   END

CLOSE ISTTabs
DEALLOCATE ISTTabs
0
 
trevorayAuthor Commented:
oops, sorry, thought i copied and pasted the errors. here they are:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'CHECK_CONSTRAINTS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'COLUMN_DOMAIN_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'COLUMN_PRIVILEGES'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'COLUMNS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'CONSTRAINT_COLUMN_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'CONSTRAINT_TABLE_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DOMAIN_CONSTRAINTS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DOMAINS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'KEY_COLUMN_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'REFERENTIAL_CONSTRAINTS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SCHEMATA'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'TABLE_CONSTRAINTS'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'TABLE_PRIVILEGES'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'TABLES'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'VIEW_COLUMN_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'VIEW_TABLE_USAGE'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'VIEWS'.
0
 
trevorayAuthor Commented:
ok, cool. i added your restriction to only do tables and the query came back in only a minute with no errors. i think i have a winner! thanks!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 14
  • 9
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now