Get Row Size of all the tables of All the DBs on a server

Get Row Size of all the tables of All the DBs on a server

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), tblName nvarchar(200), cols int, maxRowSize int)

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT  @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

      SELECT @dbTmp = dbName from #dbTmp where id = @cnt

           

      SELECT @var = 'INSERT INTO #dbName (dbName, tblName,cols, maxRowSize) '

     

      SELECT @var = @var + 'SELECT ''' + @dbTmp +''', OBJECT_NAME (sc.[object_id]) tablename , COUNT (1) nr_columns , SUM (sc.max_length) maxrowlength FROM '

               +@dbTmp+'.sys.columns sc join ' + @dbTmp + '.sys.objects so on sc.[object_id] = so.[object_id] WHERE so.type = ''U'' GROUP BY OBJECT_NAME (sc.[object_id]) ORDER BY SUM (sc.max_length) desc'

      PRINT @var

     

   EXECUTE (@var)   

      SELECT @cnt =  @cnt - 1      

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName

--DB