|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[getSpaceUsedSingleDB] -- имя БД @name nvarchar(24) AS BEGIN SET NOCOUNT ON -- Create a tempory table DECLARE @SpaceUsed TABLE ( name nvarchar(24), db_size nvarchar(13), owner nvarchar(24), dbid smallint, created char(11), status varchar(340), compatibility_level tinyint ) |
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT --CONVERT(float( ((dbsize + logsize) * 8192 / 1048576 ) РазмерБазы FROM ( SELECT SUM(CONVERT(real ,CASE WHEN status & 64 = 0 THEN size ELSE 0 END)) dbsize , SUM(CONVERT(REAL ,CASE WHEN status & 64 <> 0 THEN size ELSE 0 END)) logsize FROM dbo.sysfiles ) big |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DBCC UPDATEUSAGE (0); CREATE TABLE #t( [имя таблицы] varchar(255) ,[строк] varchar(255) ,[зарезервировано] varchar(255) ,[всего данных] varchar(255) ,[размер индексов] varchar(255) ,[свободно] varchar(255) ); INSERT INTO #t exec sp_msforeachtable N'exec sp_spaceused ''?'''; SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC; DROP TABLE #t; |