I wrote a quick and dirty script a while back. I use it to track table growth. Every week I add records to this tableUSE [dba]GOCREATE TABLE [dbo].[AllTableHistory]( [db] [sysname] NOT NULL, [schemaName] [sysname] NOT NULL, [tbl] [sysname] NOT NULL, [rows] [int] NOT NULL, [dt] [smalldatetime] NOT NULL, [reservedKB] [int] NULL, [dataKB] [int] NULL, [index_sizeKB] [int] NULL, [unusedKB] [int] NULL, CONSTRAINT [pk_allTblHist_100] PRIMARY KEY CLUSTERED ( [db] ASC, [schemaName] ASC, [tbl] ASC, [dt] ASC ))GO
And here's the script to populate it-- hold space used valuesCreate table #t ( tbl sysname, rows int, reserved varchar(32), data varchar(32), index_size varchar(32), unused varchar(32))declare @db sysnamedeclare @schema sysnamedeclare @tbl sysname-- get all tables, schema, rowcountsDeclare c CursorRead_OnlyFOR select name from sys.databases where database_id > 4open cfetch next from c into @dbwhile @@FETCH_STATUS = 0begin EXEC(' INSERT INTO AllTableHistory (db, schemaname, tbl, rows, dt) SELECT ''' + @db + ''', s.name, t.name, max(rows), DATEADD(day, 0, datediff(day, 0, getdate()-1)) FROM [' + @db + '].sys.[tables] t JOIN [' + @db + '].sys.[partitions] p on t.object_id = p.object_id JOIN [' + @db + '].sys.[schemas] s on s.schema_id = t.schema_id group by s.name, t.name ') fetch next from c into @dbendclose cdeallocate c-- get space used infoDECLARE c Cursor Read_OnlyFOR SELECT db, schemaname, tbl from AllTableHistory WHERE dt = DATEADD(day, 0, datediff(day, 0, getdate()-1)) ORDER BY dbOpen cFetch Next From c INTO @db, @schema, @tblwhile @@FETCH_STATUS = 0begin truncate table #t insert #t exec ( 'use [' + @db + ']; exec sp_spaceused ''' + @schema + '.' + @tbl + '''') update AllTableHistory set reservedKB = Convert(int, Replace(t.reserved, 'KB', '')), dataKB = Convert(int, Replace(t.data, 'KB', '')), index_sizeKB = Convert(int, Replace(t.index_size, 'KB', '')), unusedKB = Convert(int, Replace(t.unused, 'KB', '')) from #t t join AllTableHistory h on t.tbl = h.tbl where h.db = @db And h.schemaName = @schema and h.dt = DATEADD(day, 0, datediff(day, 0, getdate()-1)) Fetch Next From c INTO @db, @schema, @tblendclose cdeallocate cdrop table #t
Now, none of this does any good if you aren't actively monitoring it.Also, I restore a LOT of production databases to a server that is used for (among other things) (1) analyzing data patterns and (2) making sure our backups will restore I have a job that restores many of our most important databases every night.Hope this helps. Here is a very simple ad-hoc query you can run (again using a cursor but it returns pretty quickCreate table #t ( tbl sysname, rows int, reserved varchar(32), data varchar(32), index_size varchar(32), unused varchar(32))Declare @tbl sysnameDeclare c Cursorread_onlyfor select name from sys.tables --select name from sysobjects where type = 'u'open cfetch next from c into @tblwhile @@fetch_status = 0begin insert #t exec sp_spaceused @tbl fetch next from c into @tblEndClose cDeallocate cselect * from #tdrop table #t