Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Difference between revisions of "List rowcounts for all Tables in Database"

From Wiki

Jump to: navigation, search
(Prints a rowcount and table name for each table in a database)
 
 
Line 31: Line 31:
  
 
  end
 
  end
 +
</code>
 +
 +
 +
Here is another way that I (Denis Gobo) use
 +
For a quick count I just do a ''sp_spaceused'' instead because with big tables it might take a while to run
 +
 +
Example, I run the following
 +
 +
<code lang="tsql">
 +
SELECT 'exec sp_spaceused ''' + name + ''''
 +
FROM sys.tables
 +
where Type = 'U'
 +
</code>
 +
 +
 +
That gives the following output and then I just paste that into a new query window and run it
 +
<code lang="tsql">
 +
exec sp_spaceused 'Keys'
 +
exec sp_spaceused 'History'
 +
exec sp_spaceused 'DBUpgradeHistory'
 +
exec sp_spaceused 'ConfigurationInfo'
 +
exec sp_spaceused 'Catalog'
 +
exec sp_spaceused 'DataSets'
 +
exec sp_spaceused 'UpgradeInfo'
 +
exec sp_spaceused 'SubscriptionsBeingDeleted'
 +
exec sp_spaceused 'ModelDrill'
 +
exec sp_spaceused 'Segment'
 +
exec sp_spaceused 'ServerUpgradeHistory'
 +
exec sp_spaceused 'ChunkSegmentMapping'
 +
exec sp_spaceused 'ModelPerspective'
 +
exec sp_spaceused 'CachePolicy'
 +
exec sp_spaceused 'SegmentedChunk'
 +
exec sp_spaceused 'Users'
 +
exec sp_spaceused 'ExecutionLogStorage'
 +
exec sp_spaceused 'DataSource'
 +
exec sp_spaceused 'Policies'
 +
exec sp_spaceused 'ModelItemPolicy'
 +
exec sp_spaceused 'SecData'
 +
exec sp_spaceused 'Roles'
 +
exec sp_spaceused 'PolicyUserRole'
 +
exec sp_spaceused 'Event'
 +
exec sp_spaceused 'Subscriptions'
 +
exec sp_spaceused 'ActiveSubscriptions'
 +
exec sp_spaceused 'SnapshotData'
 +
exec sp_spaceused 'ChunkData'
 +
exec sp_spaceused 'Notifications'
 +
exec sp_spaceused 'Batch'
 +
exec sp_spaceused 'Schedule'
 +
exec sp_spaceused 'ReportSchedule'
 +
exec sp_spaceused 'RunningJobs'
 +
exec sp_spaceused 'ServerParametersInstance'
 
</code>
 
</code>
 
--[[User:onpnt|onpnt]] 17:14, 15 November 2012 (GMT)
 
--[[User:onpnt|onpnt]] 17:14, 15 November 2012 (GMT)
 
  [[category:Microsoft SQL Server Admin]]
 
  [[category:Microsoft SQL Server Admin]]

Latest revision as of 21:55, 15 November 2012

  1. set nocount on
  2.  
  3. declare @int int = 1
  4.  
  5. declare @count bigint
  6.  
  7. declare @cmd nvarchar(1500)
  8.  
  9. declare @tbl table (id int identity(1,1),name sysname)
  10.  
  11. insert into @tbl select name from sys.tables where type = N'U'
  12.  
  13.  
  14.  
  15. while @int <= (Select count(*) from @tbl)
  16.  
  17.  begin
  18.  
  19.  
  20.  
  21.    set @cmd = 'select @cnt=count(*) from ' + (select name from @tbl where id = @int)
  22.  
  23.    execute sp_executesql @cmd, N'@cnt int OUTPUT', @cnt=@count OUTPUT
  24.  
  25.    set @cmd = 'Table: ' + (select name from @tbl where id = @int) + ' has a row count of ' + CAST(@count AS VARCHAR(4000))
  26.  
  27.    print @cmd
  28.  
  29.   set @int += 1
  30.  
  31.  end


Here is another way that I (Denis Gobo) use For a quick count I just do a sp_spaceused instead because with big tables it might take a while to run

Example, I run the following

  1. SELECT 'exec sp_spaceused ''' + name + ''''
  2. FROM sys.tables
  3. where Type = 'U'


That gives the following output and then I just paste that into a new query window and run it

  1. exec sp_spaceused 'Keys'
  2. exec sp_spaceused 'History'
  3. exec sp_spaceused 'DBUpgradeHistory'
  4. exec sp_spaceused 'ConfigurationInfo'
  5. exec sp_spaceused 'Catalog'
  6. exec sp_spaceused 'DataSets'
  7. exec sp_spaceused 'UpgradeInfo'
  8. exec sp_spaceused 'SubscriptionsBeingDeleted'
  9. exec sp_spaceused 'ModelDrill'
  10. exec sp_spaceused 'Segment'
  11. exec sp_spaceused 'ServerUpgradeHistory'
  12. exec sp_spaceused 'ChunkSegmentMapping'
  13. exec sp_spaceused 'ModelPerspective'
  14. exec sp_spaceused 'CachePolicy'
  15. exec sp_spaceused 'SegmentedChunk'
  16. exec sp_spaceused 'Users'
  17. exec sp_spaceused 'ExecutionLogStorage'
  18. exec sp_spaceused 'DataSource'
  19. exec sp_spaceused 'Policies'
  20. exec sp_spaceused 'ModelItemPolicy'
  21. exec sp_spaceused 'SecData'
  22. exec sp_spaceused 'Roles'
  23. exec sp_spaceused 'PolicyUserRole'
  24. exec sp_spaceused 'Event'
  25. exec sp_spaceused 'Subscriptions'
  26. exec sp_spaceused 'ActiveSubscriptions'
  27. exec sp_spaceused 'SnapshotData'
  28. exec sp_spaceused 'ChunkData'
  29. exec sp_spaceused 'Notifications'
  30. exec sp_spaceused 'Batch'
  31. exec sp_spaceused 'Schedule'
  32. exec sp_spaceused 'ReportSchedule'
  33. exec sp_spaceused 'RunningJobs'
  34. exec sp_spaceused 'ServerParametersInstance'

--onpnt 17:14, 15 November 2012 (GMT)

839 Rating: 2.3/5 (29 votes cast)