Advanced Search

Get rows coutn of all datatables of one database

/ Published in: SQL
Save to your folder(s)
This lite fiuntion is a simple organizator rowcount for all datatables for one single database.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.