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.<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 />
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)
