Script to find number of rows in every table

Leave a comment (0) Go to comments

This following script will give you rows count of all table in current databases.

Declare c cursor for select '['+U.name+'.'+O.Name+']' from sysobjects O Inner join sys.schemas U on O.UID=u.Schema_ID where type='U' and O.name <>'dtproperties' 
Declare @name varchar(255),@sql varchar(100),@id int
open c
fetch next from c into @name
Create table #TSpace(ID int identity(1,1),name varchar(255),rows int,resevered varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
while @@fetch_status = 0
begin
 set @sql = 'sp_spaceused ' +@name

 insert into #Tspace Exec(@sql)
 set @id = @@identity
 update #Tspace set name =@name where id =@id   
 fetch next from c into @name
end
close c
deallocate c
select name,rows from #Tspace order by 2 desc
drop table #tspace

OUTPUT Screenshot


Script to find number of rows in every table number of rows in current database

Note: This script will work on SQL Server 2005+ versions.

EOF - Script to find number of rows in every table, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.