SQL Server Performance

Statement

Discussion in 'T-SQL Performance Tuning for Developers' started by alex, Sep 6, 2004.

  1. alex New Member

    Hello, friends.

    I have a schema with many user defined tables. What statement should I execute in order to see all the tables together with exact number of records in them?

    Thanks.
  2. FrankKalis Moderator

    For administrative purposes, something like the following will work.<br /><pre><br />USE PUBS<br />exec sp_msforeachtable "select '?', count(*) from ?"<br /></pre><br />There is definitely more than one way to skin that cat, however, this is probably the fastest to type for the lazy admin [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Note, that sp_msforeachtable is undocumented, so I wouldn't use it in production code, only for admin purposes.<br /><br />--<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  3. sundeip New Member

    Try This..


    Declare @SQL nVarchar(100)
    Declare @Tbl Varchar(100)
    Declare @tRows Int

    Declare Abc Cursor For Select Name From Sysobjects Where XType='U'

    OPEN Abc
    FETCH NEXT FROM abc Into @Tbl
    WHILE @@FETCH_STATUS = 0
    BEGIN
    Set @SQL='Select @tRows=Sum(1) From ' + @Tbl
    FETCH NEXT FROM abc Into @Tbl
    Exec sp_executesql @SQL, N'@tRows Int OUT',@tRows Out
    Print @Tbl +Space(10)+'Rows -->' + Cast ( Isnull(@tRows,0) As Varchar(10))

    END
    CLOSE abc
    DEALLOCATE abc




    Sandy

    (DB Developer)

Share This Page