SQL SERVER - sp_spaceused

**bobobo7569** · 22/12/2011, 23h47

Bonjour,

La fonctionnalité sp_spaceused utilisée pour une base de données retourne 2 lignes :
1 - database_name / database_size / unallocated space
2 - reserved / data / index_size / unused

Je souhaiterai sauvegarder les résultats dans une table. Mais je n'y arrive pas.

Avez vous une solution ?

Bobobo

**Etienne5685** · 23/12/2011, 10h32

Bonjour,

Voici le code de la fonction sp_spaceused

tu peux le modifier pour ajouter ton insert dans ta table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
declare @id	int			-- The object id that takes up space
		,@type	character(2) -- The object type.
		,@pages	bigint			-- Working variable for size calc.
		,@dbname sysname
		,@dbsize bigint
		,@logsize bigint
		,@reservedpages  bigint
		,@usedpages  bigint
		,@rowCount bigint
		,@objname nvarchar(776) = null		-- The object we want size on.
		,@updateusage varchar(5) 		-- Param. for specifying that
 
 
/*
**  Check to see if user wants usages updated.
*/
 
if @updateusage is not null
	begin
		select @updateusage=lower(@updateusage)
 
		if @updateusage not in ('true','false')
			begin
				raiserror(15143,-1,-1,@updateusage)
				--return(1)
			end
	end
/*
**  Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
 
 
	select @dbname = db_name()
 
	/*
	**  Try to find the object.
	*/
	SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)
 
	-- Translate @id to internal-table for queue
	IF @type = 'SQ'
		SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
 
	/*
	**  Does the object exist?
	*/
	if @id is null
		begin
			raiserror(15009,-1,-1,@objname,@dbname)
			--return (1)
		end
 
 
end
 
/*
**  Update usages if user specified to do so.
*/
 
if @updateusage = 'true'
	begin
		if @objname is null
			dbcc updateusage(0) with no_infomsgs
		else
			dbcc updateusage(0,@objname) with no_infomsgs
		print ' '
	end
 
set nocount on
 
/*
**  If @id is null, then we want summary data.
*/
if @id is null
begin
	select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
		, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
		from dbo.sysfiles
 
	select @reservedpages = sum(a.total_pages),
		@usedpages = sum(a.used_pages),
		@pages = sum(
				CASE
					-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
					When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
					When a.type <> 1 Then a.used_pages
					When p.index_id < 2 Then a.data_pages
					Else 0
				END
			)
	from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
		left join sys.internal_tables it on p.object_id = it.object_id
 
	/* unallocated space could not be negative */
	select 
		database_name = db_name(),
		database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
			* 8192 / 1048576,15,2) + ' MB'),
		'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
			(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
			* 8192 / 1048576 else 0 end),15,2) + ' MB')
 
	/*
	**  Now calculate the summary data.
	**  reserved: sum(reserved) where indid in (0, 1, 255)
	** data: sum(data_pages) + sum(text_used)
	** index: sum(used) where indid in (0, 1, 255) - data
	** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
	*/
	select
		reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
		data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
		index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
		unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end
 
/*
**  We want a particular object.
*/
else
begin
	/*
	** Now calculate the summary data. 
	*  Note that LOB Data and Row-overflow Data are counted as Data Pages.
	*/
	SELECT 
		@reservedpages = SUM (reserved_page_count),
		@usedpages = SUM (used_page_count),
		@pages = SUM (
			CASE
				WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
				ELSE lob_used_page_count + row_overflow_used_page_count
			END
			),
		@rowCount = SUM (
			CASE
				WHEN (index_id < 2) THEN row_count
				ELSE 0
			END
			)
	FROM sys.dm_db_partition_stats
	WHERE object_id = @id;
 
	/*
	** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
	*/
	IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0 
	BEGIN
		/*
		**  Now calculate the summary data. Row counts in these internal tables don't 
		**  contribute towards row count of original table.
		*/
		SELECT 
			@reservedpages = @reservedpages + sum(reserved_page_count),
			@usedpages = @usedpages + sum(used_page_count)
		FROM sys.dm_db_partition_stats p, sys.internal_tables it
		WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
	END
 
	SELECT 
		name = OBJECT_NAME (@id),
		rows = convert (char(11), @rowCount),
		reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
		data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
		index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
		unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
 
end

**zinzineti** · 23/12/2011, 12h56

Envoyé par bobobo7569

Bonjour,

La fonctionnalité sp_spaceused utilisée pour une base de données retourne 2 lignes :
1 - database_name / database_size / unallocated space
2 - reserved / data / index_size / unused

Je souhaiterai sauvegarder les résultats dans une table. Mais je n'y arrive pas.

Avez vous une solution ?

Bobobo

Bonjour,

D'abord pour voir le code de cette procédure stockée système il faut exécuter la commande suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exec sp_helptext 'sp_spaceused'

Dans le code, on voit bien les étapes où les deux SELECT (les deux lignes que tu veux insérér dans une table).
Pour réaliser cette opération j'ai inséré dans la procédure :
1) la création d'une table temporaire #SpaceUsed

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create table #SpaceUsed (database_name nvarchar(128),database_size varchar(18),unallocated_space varchar(18),reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))

2) Faire une INSERT INTO dans la table temporaire #SpaceUsed en utilisant les deux SELECT

3) Mettre en commentaire les deux SELECT

Voici la procédure modifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
CREATE procedure sp_spaceused_DB --- 2003/05/19 14:00  
@objname nvarchar(776) = null,  -- The object we want size on.  
@updateusage varchar(5) = false  -- Param. for specifying that  
     -- usage info. should be updated.  
as  
 
declare @id int   -- The object id that takes up space  
  ,@type character(2) -- The object type.  
  ,@pages bigint   -- Working variable for size calc.  
  ,@dbname sysname  
  ,@dbsize bigint  
  ,@logsize bigint  
  ,@reservedpages  bigint  
  ,@usedpages  bigint  
  ,@rowCount bigint  
 
/*  
**  Check to see if user wants usages updated.  
*/  
create table #SpaceUsed (database_name nvarchar(128),database_size varchar(18),unallocated_space varchar(18),reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))  
if @updateusage is not null  
 begin  
  select @updateusage=lower(@updateusage)  
 
  if @updateusage not in ('true','false')  
   begin  
    raiserror(15143,-1,-1,@updateusage)  
    return(1)  
   end  
 end  
/*  
**  Check to see that the objname is local.  
*/  
if @objname IS NOT NULL  
begin  
 
 select @dbname = parsename(@objname, 3)  
 
 if @dbname is not null and @dbname <> db_name()  
  begin  
   raiserror(15250,-1,-1)  
   return (1)  
  end  
 
 if @dbname is null  
  select @dbname = db_name()  
 
 /*  
 **  Try to find the object.  
 */  
 SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)  
 
 -- Translate @id to internal-table for queue  
 IF @type = 'SQ'  
  SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue  
 
 /*  
 **  Does the object exist?  
 */  
 if @id is null  
  begin  
   raiserror(15009,-1,-1,@objname,@dbname)  
   return (1)  
  end  
 
 -- Is it a table, view or queue?  
 IF @type NOT IN ('U ','S ','V ','SQ','IT')  
 begin  
  raiserror(15234,-1,-1)  
  return (1)  
 end  
end  
 
/*  
**  Update usages if user specified to do so.  
*/  
 
if @updateusage = 'true'  
 begin  
  if @objname is null  
   dbcc updateusage(0) with no_infomsgs  
  else  
   dbcc updateusage(0,@objname) with no_infomsgs  
  print ' '  
 end  
 
set nocount on  
 
/*  
**  If @id is null, then we want summary data.  
*/  
if @id is null  
begin  
 select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))  
  , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))  
  from dbo.sysfiles  
 
 select @reservedpages = sum(a.total_pages),  
  @usedpages = sum(a.used_pages),  
  @pages = sum(  
    CASE  
     -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"  
     When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0  
     When a.type <> 1 Then a.used_pages  
     When p.index_id < 2 Then a.data_pages  
     Else 0  
    END  
   )  
 from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id  
  left join sys.internal_tables it on p.object_id = it.object_id  
 
 /* unallocated space could not be negative */  
 --select   
 -- database_name = db_name(),  
 -- database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))   
 --  * 8192 / 1048576,15,2) + ' MB'),  
 -- 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then  
 --  (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))   
 --  * 8192 / 1048576 else 0 end),15,2) + ' MB')  
 
 /*  
 **  Now calculate the summary data.  
 **  reserved: sum(reserved) where indid in (0, 1, 255)  
 ** data: sum(data_pages) + sum(text_used)  
 ** index: sum(used) where indid in (0, 1, 255) - data  
 ** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)  
 */  
-- select  
--  reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),  
--  data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),  
--  index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),  
--  unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')  
--> Ajouté par Etienne ZINZINDOHOUE pour réaliser l'insertion des données dans une même table temporaire #SpaceUsed , date :  23/12/2012 
 INSERT INTO #SpaceUsed (database_name,database_size,unallocated_space,reserved,data,index_size,unused ) 
 SELECT database_name = db_name(),
 database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))* 8192 / 1048576,15,2) + ' MB'),  
 'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))* 8192 / 1048576 
 else 0 end),15,2) + ' MB'),
 reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),  
 data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),  
 index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),  
 unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')   
 SELECT * FROM #SpaceUsed
 DROP TABLE #SpaceUsed
end  
 
 
/*  
**  We want a particular object.  
*/  
else  
begin  
 /*  
 ** Now calculate the summary data.   
 *  Note that LOB Data and Row-overflow Data are counted as Data Pages.  
 */  
 SELECT   
  @reservedpages = SUM (reserved_page_count),  
  @usedpages = SUM (used_page_count),  
  @pages = SUM (  
   CASE  
    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
    ELSE lob_used_page_count + row_overflow_used_page_count  
   END  
   ),  
  @rowCount = SUM (  
   CASE  
    WHEN (index_id < 2) THEN row_count  
    ELSE 0  
   END  
   )  
 FROM sys.dm_db_partition_stats  
 WHERE object_id = @id;  
 
 /*  
 ** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table  
 */  
 IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0   
 BEGIN  
  /*  
  **  Now calculate the summary data. Row counts in these internal tables don't   
  **  contribute towards row count of original table.  
  */  
  SELECT   
   @reservedpages = @reservedpages + sum(reserved_page_count),  
   @usedpages = @usedpages + sum(used_page_count)  
  FROM sys.dm_db_partition_stats p, sys.internal_tables it  
  WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;  
 END  
 --> Ajouté par Etienne ZINZINDOHOUE pour réaliser l'insertion des données dans une même table temporaire #SpaceUsed , date :  23/12/2012 
 INSERT INTO #SpaceUsed (database_name,database_size,unallocated_space,reserved,data,index_size,unused ) 
 SELECT   
 -- name = OBJECT_NAME (@id),  
  --rows = convert (char(11), @rowCount), 
  database_name = db_name(),  
  database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))   
   * 8192 / 1048576,15,2) + ' MB'),  
  'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then  
   (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))   
   * 8192 / 1048576 else 0 end),15,2) + ' MB'),  
  reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),  
  data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),  
  index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),  
  unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')  
 SELECT * FROM #SpaceUsed
 DROP TABLE #SpaceUsed 
end   
return (0) -- sp_spaceused

-->Utilisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
  USE taBase
  EXEC sp_spaceused_DB

--> Résultat

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
database_name            database_size      unallocated_space  reserved           data               index_size         unused
------------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------
taBase                   5.25 MB            1.45 MB            2616 KB            1240 KB            1096 KB            280 KB

**bobobo7569** · 05/01/2012, 20h39

Merci à vous.

J'ai ouvert un nouveau post SQL SERVER - DROP TRIGGER.

Le problème est certainement hyper basic mais je suis resté bloqué dessus.

SQL SERVER - sp_spaceused

Développement SQL Server

Discussions similaires

Partager

Partager