A script for SQL Server to be run as sysadmin or a user that have enought priviledges on all databases to list all tables :
CREATE PROCEDURE [dbo].[sp_get_tables_sizes_all_dbs] AS BEGIN --sqlserver 2005 + IF (SELECT count(*) FROM tempdb.sys.objects WHERE name = '##TABLESIZES_ALLDB')=1 BEGIN DROP TABLE ##TABLESIZES_ALLDB; END CREATE TABLE ##TABLESIZES_ALLDB ( snapdate datetime, srv nvarchar(1000), sv nvarchar(1000), _dbname nvarchar(1000), nomTable nvarchar(1000), "partition_id" bigint, "partition_number" int, lignes bigint, "memory (kB)" bigint, "data (kB)" bigint, "indexes (kb)" bigint, "data_compression" int, data_compression_desc nvarchar(1000) ) EXECUTE master.sys.sp_MSforeachdb 'USE [?]; insert into ##TABLESIZES_ALLDB select getdate() as snapdate,cast(serverproperty(''MachineName'') as nvarchar(1000)) svr,cast(@@servicename as nvarchar(1000)) sv, ''?'' _dbname, nomTable= object_name(p.object_id),p.partition_id,p.partition_number, lignes = sum( CASE When (p.index_id < 2) and (a.type = 1) Then p.rows Else 0 END ), ''memory (kB)'' = cast(ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) as float), ''data (kB)'' = ltrim(str(sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) * 8192 / 1024.,15,0)), ''indexes (kb)'' = ltrim(str((sum(a.used_pages)-sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END) )* 8192 / 1024.,15,0)),p.data_compression, p.data_compression_desc from sys.partitions p, sys.allocation_units a ,sys.sysobjects s where p.partition_id = a.container_id and p.object_id = s.id and s.type = ''U'' -- User table type (system tables exclusion) group by p.object_id,p.partition_id,p.partition_number,p.data_compression,p.data_compression_desc order by 3 desc' ; SELECT * FROM ##TABLESIZES_ALLDB END GO |