Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    54

    Unanswered: Counting Specific Tables In A Database

    I have tried to search for count sql and have seen the usual using Information_Schema or sys.indexes.

    My problem is I don't know how to specify particular tables

    I have the following table in the database
    MyDBCount with columns TableName, CurrentRowCount, PrevRowCount

    I would like a script that loops through only the tablenames in this table and populates the CurrentRowCount.

    Then update the PrevRowCount and clear out the CurrentRowCount

    How easy would this be?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *shrug*
    It uses dynamic SQL
    Code:
    CREATE TABLE dbo.counts (
       table_name     sysname NOT NULL
     , current_count  bigint  NOT NULL
     , previous_count bigint
     ,   CONSTRAINT pk_counts PRIMARY KEY CLUSTERED (table_name)
    )
    
    INSERT INTO dbo.counts (table_name, current_count) VALUES ('calendar', 0)
    INSERT INTO dbo.counts (table_name, current_count) VALUES ('numbers' , 0)
    
    DECLARE @table_name sysname
          , @sql        varchar(max)
          , @cmd        varchar(max)
    
    SET @sql = '
    UPDATE dbo.counts
    SET    previous_count = current_count
         , current_count = (SELECT Count(*) FROM $table_name)
    WHERE  table_name = ''$table_name'''
    
    DECLARE counts_cursor CURSOR FOR
      SELECT t.name As table_name
      FROM   sys.tables As t
       INNER
        JOIN dbo.counts As c
          ON t.name = c.table_name
    
    OPEN counts_cursor
    
    FETCH NEXT FROM counts_cursor INTO @table_name
    
    WHILE @@Fetch_Status = 0
      BEGIN
        SET @cmd = Replace(@sql, '$table_name', @table_name)
        EXEC ( @cmd )
        FETCH NEXT FROM counts_cursor INTO @table_name
      END
    
    CLOSE counts_cursor
    DEALLOCATE counts_cursor
    
    SELECT *
    FROM   dbo.counts
    
    GO
    DROP TABLE dbo.counts
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •