Posted By

achavesm302 on 01/14/16


Tagged

sql count databases rows dataTables


Versions (?)

Get rows coutn of all datatables of one database


 / Published in: SQL
 

This lite fiuntion is a simple organizator rowcount for all datatables for one single database.

  1. DECLARE @id AS NUMERIC
  2.  
  3. DECLARE @name AS NVARCHAR (MAX)
  4.  
  5. DECLARE @xtype AS CHAR
  6.  
  7. DECLARE @crdate AS DATETIME
  8.  
  9. DECLARE @sqlint AS NUMERIC
  10.  
  11. DECLARE @paramDefinition AS NVARCHAR (MAX)
  12.  
  13. DECLARE @paramValue AS NVARCHAR (MAX)
  14.  
  15. CREATE TABLE ##specsAndModel (tablename NVARCHAR (MAX), value NUMERIC)
  16.  
  17. DECLARE
  18. tblCursor CURSOR FOR
  19. SELECT id,
  20. name,
  21. xtype,
  22. crdate
  23. FROM sys.sysobjects
  24. WHERE xtype = N'U'
  25.  
  26. OPEN tblCursor
  27.  
  28. FETCH NEXT FROM tblCursor
  29. INTO @id, @name, @xtype, @crdate
  30.  
  31. WHILE @@FETCH_STATUS = 0
  32. BEGIN
  33. DECLARE @sql AS NVARCHAR (MAX)
  34. SET @sql =
  35. 'insert into ##specsAndModel SELECT @tablename , count(1) FROM '
  36. + @name
  37.  
  38. SET @paramDefinition = '@tablename nvarchar(max)'
  39. SET @paramValue = @name
  40.  
  41. EXEC sp_executesql @sql, @paramDefinition, @paramValue
  42.  
  43. FETCH NEXT FROM tblCursor
  44. INTO @id, @name, @xtype, @crdate
  45. END
  46.  
  47.  
  48. CLOSE tblCursor
  49. DEALLOCATE tblCursor
  50.  
  51. SELECT tablename, value
  52. FROM ##specsAndModel
  53. GROUP BY tablename, value
  54. HAVING value > 0
  55. ORDER BY value DESC
  56.  
  57. DROP TABLE ##specsAndModel

Report this snippet  

You need to login to post a comment.