Advanced Search

Get record count for a specific database

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. --Specify the name of the database to count in the following line
  2. USE AdventureWorks; --Added by James_DBA
  3. GO
  4.  
  5. --Code modified from original posting on SQLServerCentral.Com
  6. --URL: http://www.sqlservercentral.com/scripts/Miscellaneous/30324/
  7. SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
  8. FROM sysobjects o, sysindexes i
  9. WHERE i.id = o.id
  10. AND indid IN(0,1)
  11. --This specifies 'user' databases only
  12. AND xtype = 'u' --Added by James_DBA
  13. --This omits the diagrams table of the database
  14. --You may find other system tables will need to be ommitted,
  15. --you would just name them all here using the <> operator
  16. --i.e. o.name <> dtproperties, o.name <> 'sysdiagrams'
  17. AND o.name <> 'sysdiagrams' --Added by James-DBA
  18.  
  19. --You could also look further into filtering out temp tables,
  20. --or user specified tables
  21. ORDER BY i.rowcnt DESC --I found it more useful to display 
  22. --the results by 'Row Count' Descending
  23. --The original posting suggested to sort by Table name by
  24. --using the following line, instead of the line I use above:
  25. --ORDER BY o.name
  26.  
  27. --The following line adds up all the rowcount results and places
  28. --the final result into a seperate column (below the first resulting table)
  29. COMPUTE SUM(i.rowcnt); --Added by James_DBA
  30. GO

URL: http://www.sqlservercentral.com/scripts/Administration/61766/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.