SQL Server Performance Forum – Threads Archive

Statement

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.

For administrative purposes, something like the following will work. <pre> USE PUBS exec sp_msforeachtable "select ‘?’, count(*) from ?" </pre> 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=’

‘ />] Note, that sp_msforeachtable is undocumented, so I wouldn’t use it in production code, only for admin purposes. – –Frank http://www.insidesql.de</a>

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)

]]>