Решение:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
declare @RowCount int, @tablename varchar(100) declare @Tables table ( PK int IDENTITY(1,1), tablename varchar(100), processed bit ) INSERT into @Tables (tablename) SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc declare @Space table ( name varchar(100), rows nvarchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100) ) WHILE ((select COUNT(*) from @Tables)<> 0) BEGIN declare @ft nvarchar(100) set @ft=(select top 1 tableName from @Tables) insert into @Space exec sp_spaceused @ft delete from @Tables where tablename=@ft END update @Space set data = replace(data, ' KB', '') update @Space set data = convert(int, data)/1000 update @Space set data = data + ' MB' update @Space set reserved = replace(reserved, ' KB', '') update @Space set reserved = convert(int, reserved)/1000 update @Space set reserved = reserved + ' MB' select * from @Space order by convert(int, replace(data, ' MB', '')) desc нагуглил эту строчку exec sp_spaceused 'tablename' |