Results 1 to 3 of 3

Thread: Record Count

  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Record Count

    Hi All,

    I have written the below query to know the record count of all tables for a particular DB.

    select N = IDENTITY(5), name into #tables
    from sysobjects
    where type = 'U'

    Create table #Result
    (
    TableName varchar(50),
    RecordCount int
    )

    Declare @Sql varchar(2000),@TableName varchar(50)
    Declare @Min int,@Max int
    set @Min = 1
    select @Max = max(N) from #tables


    while @Min <= @Max
    Begin

    Select @TableName = name from #tables
    where N = @Min

    Select @Sql = 'Insert into #Result Select ' + ''''+@TableName+'''' + ',count(*) from ' + @TableName
    print @Sql
    exec (@Sql)
    select @Min = @Min + 1
    End

    is there any other way to do the same job ?

    Inputs are welcome !

  2. #2
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Any inputs ?

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Try the following - it's not 100% accurate but it's close :
    Code:
            select  tab = o.name, cnt = sum(rowcnt(i.doampg))
            from    sysobjects o, sysindexes i
            where   o.id=i.id
                    and o.type='U'
                    and not ( o.sysstat2 & 1024 = 1024 )
            group by o.name
            order by o.name
    MBK

Posting Permissions

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