Solved

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

Posted on 2004-04-13
35
2,688 Views
Last Modified: 2007-12-19
can someone tell me how i can loop through each table in my database and return a record count for each table?

thanks!
0
Comment
Question by:trevoray
  • 14
  • 9
  • 5
  • +3
35 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
If you just want  a quick count....

sp_msforeachtable 'select count(*) from ?'
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Actually, this is a little better:

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


0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Notice those are double single quotes around the first ?
0
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
also consider using
exec sp_MSForEachTable 'EXEC sp_spaceused @objname=''?'' '

0
 

Author Comment

by:trevoray
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
i'd rather not use a stored procedure
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
Like I said above--without creating new tables, in query analyzer run sp_msforeachtable 'select ''?'' as tablename,count(*) from ?'
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
You're going to have to create a cursor .. (nasty..)

but here it is.. (WIP)..
0
 

Author Comment

by:trevoray
Comment Utility
if i did run the sp_msforeachtable sp, what would i put in place of the '?'   ?
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility

/*
**  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
 
LVL 26

Expert Comment

by:Hilaire
Comment Utility
>>what would i put in place of the '?'  <<
Leave the  '?' as is, it is substituted with the actual table_name inside the loop
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
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
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
ok. just found out i don't have permission to create sp.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
ok. dan, i'm trying your latest now. should it take a while to run? it seems to be taking a while.
0
 

Author Comment

by:trevoray
Comment Utility
it's at 2:35 dan and running

i'm getting nervous...
0
 

Author Comment

by:trevoray
Comment Utility
4:10 ....
0
 

Author Comment

by:trevoray
Comment Utility
ok, it's working. cool! thanks!
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
oh. it's going through all the views too
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
dan,

i got all these errors. can you tell me how to avoid these errors AND not count the views?
0
 

Author Comment

by:trevoray
Comment Utility
oh ok, i see your above post on how to elimnate views, how can i eliminate the errors?
0
 

Expert Comment

by:mbwatkins
Comment Utility
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
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
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
 

Author Comment

by:trevoray
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now