if exists (select * from sysobjects where id = object_id(N'Indoo_template_0117_set') and OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN drop procedure Indoo_template_0117_set END GO CREATE PROCEDURE Indoo_template_0117_set AS /* Obtains spaced used data for ALL user tables in the database */ DECLARE @TableName VARCHAR(100) --For storing values in the cursor --Cursor to get the name of all user tables from the sysobjects listing DECLARE tableCursor CURSOR FOR select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 FOR READ ONLY --A procedure level temp table to store the results CREATE TABLE #TempTable ( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) ) --Open the cursor OPEN tableCursor --Get the first table name from the cursor FETCH NEXT FROM tableCursor INTO @TableName --Loop until the cursor was not able to fetch WHILE (@@Fetch_Status >= 0) BEGIN --Dump the results of the sp_spaceused query to the temp table INSERT #TempTable EXEC sp_spaceused @TableName --Get the next table name FETCH NEXT FROM tableCursor INTO @TableName END --Get rid of the cursor CLOSE tableCursor DEALLOCATE tableCursor if exists (select * from sysobjects where id = object_id(N'Indoo_template_0117') and OBJECTPROPERTY(id, N'IsTable') = 1) BEGIN drop table Indoo_template_0117 END select * into Indoo_template_0117 from (select top 100 PERCENT Table_Name = tableName, rows = cast(numberofRows as bigint), reserved_KB = cast(replace(reservedSize,' KB','') as bigint), data_KB = cast(replace(dataSize,' KB','') as bigint), index_size_KB = cast(replace(indexSize,' KB','') as bigint), unused_KB = cast(replace(unusedSize,' KB','') as bigint) from #TempTable ) as abc --Final cleanup! DROP TABLE #TempTable GO exec Indoo_template_0117_set binaryData.fileSytemDBPath gridView.recordcount 1 gridView.inlineEdit version 01.05.00 connectionType 0 connectionString tablename Indoo_template_0117 tableKeyColumn reserved_kb showGridView on gridview.bulkimport gridview.bulkexport gridview.bulkdelete gridview.insertrecord gridview.editrecord gridview.deleterecord gridview.viewrecord hidegridview gridView.hideElement 0 gridView.pagerecord showGridEdit on hidegridviewonedit grideditshowabovegridview editbuttonsinlastcoulumns gridedit.captionposition gridedit.checkwebpageonexit gridEdit.mode gridEdit.valueID gridEdit.TextboxWidth gridEdit.redirectURL gridEdit.txt_Save gridEdit.txt_Insert gridEdit.txt_Back gridEdit.txt_Next gridEdit.txt_Finish gridEdit.txt_cancel gridEdit_customHTMLForm gridEdit.isBinary networkCredential.userName networkCredential.passWord sqldeveloperwindowcb importexportcb on gridView.importStopAfterErrorCount 10 isEmptyCaptionReplaced on gridedit.allowunecryptedquerystring gridedit.isgridvieweditredirected gridview.ispagenumbertextbox gridview.sqlstatementquery gridView.gridDdlForm 0 alternateTablename alternateConnectionString gridView.ID gridEdit.ID gridEdit.validRedirectURLSetParameter gridEdit.validRedirectURLDelParameter gridEdit.sendToMail gridView.OrderBy reserved_KB DESC gridView.SqlWhere moduleHeader [sqlQuery:EXEC Indoo_template_0117_set] moduleFooter <span style="color: red">Total reserved space: <command format="N" sql="SELECT sum(reserved_kb) FROM Indoo_template_0117 [indoogrid:gridViewWhereClause]"></command> KB</span> htmlHeadBlock startupScriptBlock onSubmitScriptBlock sqlOnInsertRecord sqlOnUpdateRecord sqlOnUpdateFinishRecord sqlOnViewModule sqlOnViewRecord gridViewColumn.0.columnName Table_Name gridViewColumn.0.Visible on gridViewColumn.0.allowExport on gridViewColumn.0.allowEdit on gridViewColumn.0.allowFilter 1 gridViewColumn.0.allowSort on gridViewColumn.0.allowImport on gridViewColumn.0.isOther 0 gridViewColumn.1.columnName rows gridViewColumn.1.Visible on gridViewColumn.1.allowExport on gridViewColumn.1.allowEdit on gridViewColumn.1.allowFilter 1 gridViewColumn.1.allowSort on gridViewColumn.1.allowImport on gridViewColumn.1.isOther 0 gridViewColumn.2.columnName reserved_KB gridViewColumn.2.Visible on gridViewColumn.2.allowExport on gridViewColumn.2.allowEdit on gridViewColumn.2.allowFilter 1 gridViewColumn.2.allowSort on gridViewColumn.2.allowImport on gridViewColumn.2.isOther 0 gridViewColumn.3.columnName data_KB gridViewColumn.3.Visible on gridViewColumn.3.allowExport on gridViewColumn.3.allowEdit on gridViewColumn.3.allowFilter 1 gridViewColumn.3.allowSort on gridViewColumn.3.allowImport on gridViewColumn.3.isOther 0 gridViewColumn.4.columnName index_size_KB gridViewColumn.4.Visible on gridViewColumn.4.allowExport on gridViewColumn.4.allowEdit on gridViewColumn.4.allowFilter 1 gridViewColumn.4.allowSort on gridViewColumn.4.allowImport on gridViewColumn.4.isOther 0 gridViewColumn.5.columnName unused_KB gridViewColumn.5.Visible on gridViewColumn.5.allowExport on gridViewColumn.5.allowEdit on gridViewColumn.5.allowFilter 1 gridViewColumn.5.allowSort on gridViewColumn.5.allowImport on gridViewColumn.5.isOther 0 gridViewColumn.0.caption gridViewColumn.1.caption gridViewColumn.2.caption gridViewColumn.3.caption gridViewColumn.4.caption gridViewColumn.5.caption notSkipSqlOnImport on binaryData.fileSytemDBPath |DataDirectory|indooGrid\ gridView.recordcount 1 gridView.inlineEdit version 01.05.00 connectionType 0 connectionString tablename Indoo_template_0117 tableKeyColumn reserved_kb showGridView on gridview.bulkimport gridview.bulkexport gridview.bulkdelete gridview.insertrecord gridview.editrecord gridview.deleterecord gridview.viewrecord hidegridview gridView.hideElement 0 gridView.pagerecord showGridEdit on hidegridviewonedit grideditshowabovegridview editbuttonsinlastcoulumns gridedit.captionposition gridedit.checkwebpageonexit gridEdit.mode gridEdit.valueID gridEdit.TextboxWidth gridEdit.redirectURL gridEdit.txt_Save gridEdit.txt_Insert gridEdit.txt_Back gridEdit.txt_Next gridEdit.txt_Finish gridEdit.txt_cancel gridEdit_customHTMLForm gridEdit.isBinary networkCredential.userName networkCredential.passWord sqldeveloperwindowcb importexportcb on gridView.importStopAfterErrorCount 10 isEmptyCaptionReplaced on gridedit.allowunecryptedquerystring gridedit.isgridvieweditredirected gridview.ispagenumbertextbox gridview.sqlstatementquery gridView.gridDdlForm 0 alternateTablename alternateConnectionString gridView.ID gridEdit.ID gridEdit.validRedirectURLSetParameter gridEdit.validRedirectURLDelParameter gridEdit.sendToMail gridView.OrderBy reserved_KB DESC gridView.SqlWhere moduleHeader [sqlQuery:EXEC Indoo_template_0117_set] moduleFooter <span style="color: red">Total reserved space: <command format="N" sql="SELECT sum(reserved_kb) FROM Indoo_template_0117 [indoogrid:gridViewWhereClause]"></command> KB</span> htmlHeadBlock startupScriptBlock onSubmitScriptBlock sqlOnInsertRecord sqlOnUpdateRecord sqlOnUpdateFinishRecord sqlOnViewModule sqlOnViewRecord gridViewColumn.0.columnName Table_Name gridViewColumn.0.Visible on gridViewColumn.0.allowExport on gridViewColumn.0.allowEdit on gridViewColumn.0.allowFilter 1 gridViewColumn.0.allowSort on gridViewColumn.0.allowImport on gridViewColumn.0.isOther 0 gridViewColumn.1.columnName rows gridViewColumn.1.Visible on gridViewColumn.1.allowExport on gridViewColumn.1.allowEdit on gridViewColumn.1.allowFilter 1 gridViewColumn.1.allowSort on gridViewColumn.1.allowImport on gridViewColumn.1.isOther 0 gridViewColumn.2.columnName reserved_KB gridViewColumn.2.Visible on gridViewColumn.2.allowExport on gridViewColumn.2.allowEdit on gridViewColumn.2.allowFilter 1 gridViewColumn.2.allowSort on gridViewColumn.2.allowImport on gridViewColumn.2.isOther 0 gridViewColumn.3.columnName data_KB gridViewColumn.3.Visible on gridViewColumn.3.allowExport on gridViewColumn.3.allowEdit on gridViewColumn.3.allowFilter 1 gridViewColumn.3.allowSort on gridViewColumn.3.allowImport on gridViewColumn.3.isOther 0 gridViewColumn.4.columnName index_size_KB gridViewColumn.4.Visible on gridViewColumn.4.allowExport on gridViewColumn.4.allowEdit on gridViewColumn.4.allowFilter 1 gridViewColumn.4.allowSort on gridViewColumn.4.allowImport on gridViewColumn.4.isOther 0 gridViewColumn.5.columnName unused_KB gridViewColumn.5.Visible on gridViewColumn.5.allowExport on gridViewColumn.5.allowEdit on gridViewColumn.5.allowFilter 1 gridViewColumn.5.allowSort on gridViewColumn.5.allowImport on gridViewColumn.5.isOther 0 gridViewColumn.0.caption gridViewColumn.1.caption gridViewColumn.2.caption gridViewColumn.3.caption gridViewColumn.4.caption gridViewColumn.5.caption notSkipSqlOnImport on