Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    28

    Unanswered: What is wrong with my code?

    I want to print out the table name and record count whenever a table has record(s), but the statement:
    Print 'TableName: '+@TableName +' Row Count: '+CAST(@RCTR AS varchar)
    never gets executed.

    Any help is appreciated.

    ******************************************
    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int

    SET @TableName = ''
    SET @RCTR = 0

    WHILE @TableName IS NOT NULL
    BEGIN
    SET @TableName =
    (
    SELECT MIN(QUOTENAME(TABLE_NAME))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
    OBJECT_ID(QUOTENAME(TABLE_NAME)
    ), 'IsMSShipped') = 0
    )

    SET @SQL = ( 'select count(*) from ' + @TableName )
    EXEC (@SQL)
    SET @RCTR = @@ROWCOUNT
    IF @RCTR > 0
    Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar)
    END
    *********************************

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    It did. But the @@ROWCOUNT will always be 1
    See the Messages tab of Query Analyser
    -----------------
    KH


  3. #3
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Here is a modifed version

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @SQL nvarchar(4000), @RCTR int

    SET @TableName = ''
    SET @RCTR = 0

    create table #temp(table_name varchar(100), rec_cnt int)

    WHILE @TableName IS NOT NULL
    BEGIN
    SET @TableName =
    (
    SELECT MIN(QUOTENAME(TABLE_NAME))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )

    SET @SQL = ( 'select ''' + @TableName + ''', count(*) from ' + @TableName )
    insert into #temp EXEC (@SQL)
    SET @RCTR = @@ROWCOUNT
    IF @RCTR > 0
    Print 'TableName: ' + @TableName + ' Row Count: ' + CAST(@RCTR AS varchar)
    END

    select * from #temp
    -----------------
    KH


  4. #4
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    there is an undocumented SP (sp_msforeachtable) that loops thru each table. it can be used to get the same result as below

    exec sp_msforeachtable "declare @xx int set @xx=(select count(*) from ?) print 'table '+ '?' + ' rows=' + cast(@xx as varchar)"

  5. #5
    Join Date
    Dec 2005
    Posts
    28
    Thanks all. Get the problem fixed.

Posting Permissions

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