Posted By

achavesm302 on 01/14/16

Tagged

Versions (?)

Last Edited at 01/14/16 11:02am

Statistics

Viewed 575 times

Favorited by 0 user(s)

Related snippets

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.

Highlighted TextExpand

DECLARE @id AS NUMERIC

DECLARE @name AS NVARCHAR (MAX)

DECLARE @xtype AS CHAR

DECLARE @crdate AS DATETIME

DECLARE @sqlint AS NUMERIC

DECLARE @paramDefinition AS NVARCHAR (MAX)

DECLARE @paramValue AS NVARCHAR (MAX)

CREATE TABLE ##specsAndModel (tablename NVARCHAR (MAX), value NUMERIC)

DECLARE
   tblCursor CURSOR FOR
      SELECT id,
             name,
             xtype,
             crdate
        FROM sys.sysobjects
       WHERE xtype = N'U'

OPEN tblCursor

FETCH NEXT FROM tblCursor
INTO @id, @name, @xtype, @crdate

WHILE @@FETCH_STATUS = 0
   BEGIN
      DECLARE @sql AS NVARCHAR (MAX)
      SET @sql =
             'insert into ##specsAndModel SELECT @tablename , count(1)  FROM '
             + @name

SET @paramDefinition = '@tablename nvarchar(max)'
      SET @paramValue = @name

EXEC sp_executesql @sql, @paramDefinition, @paramValue

FETCH NEXT FROM tblCursor
      INTO @id, @name, @xtype, @crdate
   END

CLOSE tblCursor
DEALLOCATE tblCursor

SELECT tablename, value
    FROM ##specsAndModel
GROUP BY tablename, value
  HAVING value > 0
ORDER BY value desc

DROP TABLE ##specsAndModel

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.