Pre 2012 Stored Procedure: Need some DEV advice (COMPUTE BY TO WITH ROLL UP)

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  June 13, 2014 at 10:47 am

  #287448

  It's not my program, but it's a really nice Stored Procedure that was written by Richard Ding back in 2008 called sp_sos. It basically is a nice way to display all the size calcs on your database tables (data and index). I have not been able to find a 2012 friendly version of it (due to it using a COMPUTE BY aggregate at the end of the PROC), but was hoping someone out here might have some idea on how to re-write it using the ROLL UP function or some other approach. Here is the section of code in question, but I'll attach a copy of the whole PROC as well...

  UPDATE ##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'

  IF @Count = 1 -- when only 1 row, no need to sum up total

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  ELSE

  BEGIN

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])

  END

  END

  RETURN (0)

  GO

  Thank you in advance!

  SQL_ME_RICH

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 14, 2014 at 2:54 am

  #1721716

  Simply comment the compute part and replace with a SELECT statement

  -- COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])

  SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM dbo.##FO

  Would work for all SQL Server versions.

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  June 17, 2014 at 10:22 am

  #1722398

  Sachin - thank you for starting things out here with this logic. I appreciate it! I am still getting compile errors (though it will make the Stored Procedure now) in that the bracketed columns are showing to be invalid. When I try to run it after compiling it, it comes back with the following error:

  Msg 50000, Level 16, State 1, Procedure sp_SOS, Line 259

  No records were found macthcing your criteria.

  There definitely are matching records, so I once again am - stuck.

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 17, 2014 at 10:45 am

  #1722406

  The error is not because of the change.The error is thrown by the SP instead.Try searching for text "No records were found macthcing your criteria" in the SP.

  matching in misspelled as "macthcing" in the SP.

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  June 17, 2014 at 11:03 am

  #1722420

  Now I just get the same error with the word 'matching' correctly spelled. That is just the literal string that SQL calls if it does not find matching records.

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  July 28, 2014 at 10:59 am

  #1732934

  Reviving this thread in the hope of finding a solution. I have used a Stored Procedure by Richard Ding for the past few years called sp_SOS. It works great on SQL 2008 R2 down, but from SQL 2012 (and I am guessing SQL 2014 as well) it will not work. The issue is with the SP using a COMPUTE statement in it's logic. I was advised to simply change that to a SELECT statement and that it would run, but I have had no such success. I have included the full code this time (was simply attached in an earlier thread) in hopes that someone might be able to get this to work. Here is the code (both the old COMPUTE line and new SELECT line toward the bottom of the script are included):

  USE [master]

  GO

  /****** Object: StoredProcedure [dbo].[sp_SOS] Script Date: 7/28/2014 9:54:24 AM ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER PROCEDURE [dbo].[sp_SOS]

  @DbName sysname = NULL,

  @SchemaName sysname = NULL,

  @ObjectName sysname = N'%',

  @TopClause nvarchar(20) = NULL,

  @ObjectType nvarchar(50) = NULL,

  @ShowInternalTable nvarchar(3) = NULL,

  @OrderBy nvarchar(100) = NULL,

  @UpdateUsage bit = 0

  AS

  /*=================================================================================================

  Author: Richard Ding

  Created: Mar. 03, 2008

  Modified: Mar. 17, 2008

  Purpose: Manipulate object size calculation and display for SS 2000/2005/2008

  Parameters:

  @DbName: default is the current database

  @SchemaName: default is null showing all schemas

  @ObjectName: default is "%" including all objects in "LIKE" clause

  @TopClause: default is null showing all objects. Can be "TOP N" or "TOP N PERCENT"

  @ObjectType: default is "S", "U", "V", "SQ" and "IT". All objects that can be sized

  @ShowInternalTable: default is "Yes", when listing IT, the Parent excludes it in size

  @OrderBy: default is by object name, can be any size related column

  @UpdateUsage: default is 0, meaning "do not run DBCC UPDATEUSAGE"

  Note: SS 2000/2005/2008 portable using dynamic SQL to bypass validation error;

  Use ISNULL to allow prefilled default parameter values;

  Use "DBCC UPDATEUSAGE" with caution as it can hold up large databases;

  Unicode compatible and case insensitive;

  Sample codes:

  EXEC dbo.sp_SOS;

  EXEC dbo.sp_SOS 'AdventureWorks', NULL, '%', NULL, 'U', 'No', 'T', 1;

  sp_SOS 'TRACE', NULL, NULL, Null, ' ,,, ,;SQ,; u ;;;,, v ,,;iT , ;', 'No', N'N', 0;

  sp_SOS NULL, NULL, NULL, NULL, 'U', 'Yes', N'U', 1;

  sp_SOS 'AdventureWorks', 'Person%', 'Contact%', NULL, 'U', 'no', 'N', 0;

  sp_SOS 'AdventureWorks', NULL, NULL, N'Top 100 Percent', 'S', 'yes', N'N', 1;

  sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT', 'yes', 'N', 1;

  sp_SOS 'TRACE', NULL, 'Vw_DARS_217_overnight_activity_11142007', ' top 10 ', 'v', 'yes', 'N', 0;

  sp_SOS 'AdventureWorks', NULL, 'xml%', ' top 10 ', null, 'yes', 'N', 1;

  sp_SOS 'AdventureWorks2008', NULL, 'sales%', NULL, ' ,,; u ;;;,, v ', 'No', N'N', 1;

  sp_SOS NULL, NULL, NULL, N'Top 100 Percent', ' ;;Q, U;V,', N'Y', 1;

  =================================================================================================*/

  SET NOCOUNT ON;

  -- Input parameter validity checking

  DECLARE @SELECT nvarchar(2500),

  @WHERE_Schema nvarchar(200),

  @WHERE_Object nvarchar(200),

  @WHERE_Type nvarchar(200),

  @WHERE_Final nvarchar(1000),

  @ID int,

  @Version nchar(2),

  @String nvarchar(4000),

  @Count bigint,

  @GroupBy nvarchar(450);

  IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y')

  BEGIN

  RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are:

  ''N'' --> Listing by object name

  ''R'' --> Listing by number of records

  ''T'' --> Listing by total size

  ''U'' --> Listing by used portion (excluding free space)

  ''I'' --> Listing by index size

  ''D'' --> Listing by data size

  ''F'' --> Listing by unused (free) space

  ''Y'' --> Listing by object type ', 16, 1)

  RETURN (-1)

  END;

  -- Object Type Validation and Clean up

  DECLARE @OTV nvarchar(10), @OTC nvarchar(10);

  SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,

  N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''),

  N'V', N''), N'IT', N''), N'S', N'');

  IF LEN(@OTV) <> 0 -- only allow comma, semi colon and space around S,U,V,SQ,IT

  BEGIN

  RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them,

  separated by space, comma or semicolon.

  S -> System table;

  U -> User table;

  V -> Indexed view;

  SQ -> Service Queue;

  IT -> Internal Table', 16, 1)

  RETURN (-1)

  END

  ELSE -- passed validation

  BEGIN

  SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N''))

  SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL

  (@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S',

  N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''')

  END

  ---- common ----

  SELECT @DbName = ISNULL(@DbName, DB_NAME()),

  @Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1,

  CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1),

  @OrderBy = N'ORDER BY [' +

  CASE ISNULL(@OrderBy, N'N')

  WHEN N'N' THEN N'Object Name] ASC '

  WHEN N'R' THEN N'Rows] DESC, [Object Name] ASC '

  WHEN N'T' THEN N'Total(MB)] DESC, [Object Name] ASC '

  WHEN N'U' THEN N'Used(MB)] DESC, [Object Name] ASC '

  WHEN N'I' THEN N'Index(MB)] DESC, [Object Name] ASC '

  WHEN N'D' THEN N'Data(MB)] DESC, [Object Name] ASC '

  WHEN N'F' THEN N'Unused(MB)] DESC, [Object Name] ASC '

  WHEN N'Y' THEN N'Type] ASC, [Object Name] ASC '

  END;

  ---------------------- SS 2000 -----------------------------------

  IF @Version = N'8'

  BEGIN

  SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') +

  N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''Object Name'',

  o.type AS ''Type'',

  MAX(i.[rows]) AS ''Rows'',

  CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'',

  CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'',

  CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'',

  CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END)

  * 8.000/1024)) AS ''Index(MB)'',

  CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END

  * 8.000/1024)) AS ''Data(MB)''

  FROM dbo.sysindexes i WITH (NOLOCK)

  JOIN dbo.sysobjects o WITH (NOLOCK)

  ON i.id = o.id

  WHERE i.name NOT LIKE ''_WA_Sys_%''

  AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ',

  -- SS 2000 calculation as below:

  -- "reserved" = total size;

  -- "dpages" = data used;

  -- "used" = used portion (contains data and index);

  -- text or image column: use "used" for data size

  -- Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused).

  @WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%')

  + N''' AND o.type IN (' + @ObjectType + N') ',

  @GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ',

  @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy

  END

  ------------------- ss 2k5 ------------------------------------------------------

  IF @Version IN (N'9', N'10')

  BEGIN

  SELECT @String = N'

  IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL

  DROP TABLE dbo.##BO

  CREATE TABLE dbo.##BO (

  ID int identity,

  DOI bigint null, -- Daughter Object Id

  DON sysname null, -- Daughter Object Name

  DSI int null, -- Daughter Schema Id

  DSN sysname null, -- Daughter Schema Name

  DOT varchar(10) null, -- Daughter Object Type

  DFN sysname null, -- Daughter Full Name

  POI bigint null, -- Parent Object Id

  PON sysname null, -- Parent Object Name

  PSI bigint null, -- Parent Schema Id

  PSN sysname null, -- Parent Schema Name

  POT varchar(10) null, -- Parent Object Type

  PFN sysname null -- Parent Full Name

  )

  INSERT INTO dbo.##BO (DOI, DSI, DOT, POI)

  SELECT object_id, schema_id, type, Parent_object_id

  FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'')

  USE ' + @DbName + N'

  UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI END

  UPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POI

  UPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON,

  PFN = schema_name(PSI)+ ''.'' + object_name(POI)

  '

  EXEC (@String)

  SELECT

  @WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END,

  @SELECT = N'USE ' + @DbName + N'

  SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') +

  N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN

  ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''Object Name'',

  ' + @WHERE_Type + N' AS ''Type'',

  SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN

  CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END

  ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'',

  SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''

  THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'',

  SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''

  THEN ps.reserved_page_count ELSE 0 END

  - CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN

  ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'',

  SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''

  THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'',

  SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%''

  THEN ps.used_page_count ELSE 0 END

  - CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')

  + N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2)

  THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)

  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Index(MB)'',

  SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')

  + N''' = ''No'' THEN 0 ELSE CASE WHEN (ps.index_id<2)

  THEN (ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)

  ELSE ps.lob_used_page_count + ps.row_overflow_used_page_count END END) * 8.000/1024 AS ''Data(MB)''

  FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t

  ON ps.object_id = t.DOI

  ',

  @ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'',

  ''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,

  @WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN

  @WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%')

  + ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' '

  ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END, -- DON or PON

  @WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema +

  N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',

  @GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN

  THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''

  SELECT @String = @SELECT + @WHERE_Final + @GroupBy + @OrderBy

  -- SELECT @String AS 'STRING'

  END

  ----- common ------

  IF OBJECT_ID(N'tempdb.dbo.##FO', N'U') IS NOT NULL

  DROP TABLE dbo.##FO;

  CREATE TABLE dbo.##FO (

  ID int identity,

  [Object Name] sysname,

  [Type] varchar(2),

  [Rows] bigint,

  [Total(MB)] dec(10,3),

  [-] nchar(1),

  [Unused(MB)] dec(10,3),

  [==] nchar(2),

  [Used(MB)] dec(10,3),

  [=] nchar(1),

  [Index(MB)] dec(10,3),

  [+] nchar(1),

  [Data(MB)] dec(10,3) );

  INSERT INTO ##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)])

  EXEC (@String);

  SELECT @Count = COUNT(*) FROM ##FO;

  IF @Count = 0

  BEGIN

  RAISERROR (N'No records were found matching your criteria.', 16, 1)

  RETURN (-1)

  END

  ELSE -- There're at least one records

  BEGIN

  -- Run DBCC UPDATEUSAGE to correct wrong values

  IF ISNULL(@UpdateUsage, 0) = 1

  BEGIN

  SELECT @ObjectName = N'', @ID = 0

  WHILE 1 = 1

  BEGIN

  SELECT TOP 1 @ObjectName = CASE WHEN [Object Name] LIKE N'%(%' THEN

  SUBSTRING([Object Name], 1, CHARINDEX(N'(', [Object Name])-2) ELSE [Object Name] END

  , @ID = ID FROM ##FO WHERE ID > @ID ORDER BY ID ASC

  IF @@ROWCOUNT = 0

  BREAK

  PRINT N'==> DBCC UPDATEUSAGE (' + @DbName + N', ''' + @ObjectName + N''') WITH COUNT_ROWS'

  DBCC UPDATEUSAGE (@DbName, @ObjectName) WITH COUNT_ROWS

  PRINT N''

  END

  PRINT N''

  TRUNCATE TABLE ##FO

  INSERT INTO ##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],

  [Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String)

  END

  ELSE

  PRINT N'(Warning: Run "DBCC UPDATEUSAGE" on suspicious objects. It may incur overhead on big databases.)'

  PRINT N''

  UPDATE ##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'

  IF @Count = 1 -- when only 1 row, no need to sum up total

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  ELSE

  BEGIN

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  --COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])

  SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM ##FO

  END

  END

  RETURN (0)

  Thanks to all of you for any help with this!

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  July 28, 2014 at 11:26 am

  #1732946

  This is another approach I am looking at, but I am not having much success calling it from the sp_SOS SP.

  https://sql2012computeby.codeplex.com/

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  July 28, 2014 at 12:22 pm

  #1732974

  Have now tried adding a GROUP BY 'Column' WITH ROLLUP, but still same error...

  IF @Count = 1 -- when only 1 row, no need to sum up total

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  ELSE

  BEGIN

  --EXECUTE dbo.proc_EmulateComputeQuery

  SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO ORDER BY ID ASC

  --COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])

  SELECT SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)]) FROM ##FO

  GROUP BY [Total(MB)] WITH ROLLUP

  END

  END

  RETURN (0)

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  July 28, 2014 at 5:01 pm

  #1733056

  You will need to add support for SQL2012 by changing line 163 to this:

  IF @Version IN (N'9', N'10', N'11')

  I would change the final select to this:

  SELECT [ID],[Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],

  [Index(MB)],[+],[Data(MB)]

  FROM ##FO

  union all

  SELECT MAX([ID])+1,'==========','',SUM([Rows]),SUM([Total(MB)]),[-], SUM([Unused(MB)]),[==], SUM([Used(MB)]), [=], SUM([Index(MB)]), [+], SUM([Data(MB)])

  FROM ##FO

  GROUP BY [-],[==],[=],[+]

  ORDER BY ID ASC

  And finally, I would delete this procedure and rewrite it in a safe way, using parameterised queries and sp_executesql instead of concatenating strings and using EXEC(@String) <-- this provides no protection from SQL Injection attacks.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]