Advanced Search

Return to Snippet

Revision: 70326
at January 14, 2016 11:02 by achavesm302
Initial Code 
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
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

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

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Get rows coutn of all datatables of one database

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
sql

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL