如何计算SQL Server 2005中存储过程的大小?

;WITH ROUTINES AS (
    -- CANNOT use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit
    SELECT o.type_desc AS ROUTINE_TYPE
            ,o.[name] AS ROUTINE_NAME
            ,m.definition AS ROUTINE_DEFINITION
    FROM sys.sql_modules AS m
    INNER JOIN sys.objects AS o
        ON m.object_id = o.object_id
)
SELECT SUM(LEN(ROUTINE_DEFINITION))
FROM ROUTINES

一个比计算字符稍微好一点的方法是使用informationschema.routines。您可以将每个例程定义的长度相加为(注意,每个例程定义的最大长度为4000个字符,请参阅下面没有此限制的方法):

select Sum(Len(Routine_Definition)) from information_schema.routines 
where routine_type = 'PROCEDURE'

select Len(Routine_Definition), * from information_schema.routines 
where routine_type = 'PROCEDURE'

存储过程的长度不太可能是问题所在。数据库空间不足通常是由于不备份日志文件(然后使用dbcc shrinkfile或dbcc shrinkdatabase收缩日志文件)等原因造成的。

在Sql 2000中,下面是一个例程,它提供的长度不超过4000个字符的限制:

DECLARE @Name VarChar(250)
DECLARE RoutineCursor CURSOR FOR
   select Routine_Name from information_schema.routines where routine_type = 'PROCEDURE'

DECLARE @Results TABLE
   (   SpName   VarChar(250),
       SpLength   Int
   )

CREATE TABLE ##SpText
   (   SpText   VarChar(8000)   )

OPEN RoutineCursor
FETCH NEXT FROM RoutineCursor INTO @Name

WHILE @@FETCH_STATUS = 0
   BEGIN
      INSERT INTO ##SpText   (SpText)   EXEC sp_helptext @Name

      INSERT INTO @Results (SpName, SpLength) (SELECT @Name, Sum(Len(SpText)) FROM ##SpText)
      TRUNCATE TABLE ##SpText

      FETCH NEXT FROM RoutineCursor INTO @Name
   END

CLOSE RoutineCursor
DEALLOCATE RoutineCursor
DROP TABLE ##SpText

SELECT SpName, SpLength FROM @Results ORDER BY SpLength DESC
SELECT Sum(SpLength) FROM @Results

Dave_H的解决方案在information_schema.routines表中达到了4000个字符的限制

尝试这样做,首先生成一个包含存储过程全文的表,然后对字符长度求和。

--create a temp table to hold the data
create table ##sptext (sptext varchar(1000))
go

--generate the code to insert the full text of your sprocs
select 'insert into ##sptext (sptext) exec sp_helptext '''+specific_name+''';'
from information_schema.routines 
where routine_type = 'PROCEDURE'
go

/*Copy the output back to your query analyzer and run it*/

--now sum the results    
select sum(len(sptext))
from ##sptext

需要使用DATALENGTH而不是LEN来获取字节数而不是字符数,因为sys.sql_modules目录视图的definition列是NVARCHAR(MAX),即Unicode

SELECT Type,
   SUM(Chars)  SizeChars,
   SUM(Bytes)  SizeBytes,
   SUM(Bytes) / 1024. SizeKB,
   CAST(SUM(Bytes) / 1024 AS VARCHAR) + '.' + CAST(SUM(Bytes) % 1024 AS VARCHAR) SizeKBRemBytes
FROM
(
SELECT o.type_desc Type, 
       LEN(sm.definition) Chars,
       DATALENGTH(sm.definition) Bytes
    FROM sys.sql_modules sm
    JOIN sys.objects o ON sm.object_id = o.object_id
) x
GROUP BY Type
ORDER BY Type

/*
<documentation>
  <summary>Count size in bytes veiws, triggers, procedures and function in database.</summary>
  <returns>1 data set: RoutinType, SUM LENGTH of objects, SUM DATALENGTH.</returns>
  <issues>No</issues>
  <author>Cade Roux</author>
  <created>2008-10-20</created>
  <modified>2019-06-26 by Konstantin Taranov</modified>
  <version>1.0</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Objects_Size_In_Database.sql</sourceLink>
  <originalLink>https://stackoverflow.com/a/219740/2298061</originalLink>
</documentation>
*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH CTE_Routine AS (
    /* Can not use INFORMATION_SCHEMA.ROUTINES because of 4000 character limit */
    SELECT o.type_desc     AS RoutineType
         , o.[name]        AS RoutineName
         , m.[definition]  AS RoutineDefinition
    FROM   sys.sql_modules AS m 
    INNER JOIN sys.objects AS o ON m.object_id = o.object_id
)
SELECT RoutineType
     , SUM(LEN(RoutineDefinition))            AS RoutineLen
     /* DATALENGTH for counting trailing space in the end of objects definitions */
     , SUM(DATALENGTH(RoutineDefinition)) / 2 AS RoutineDatalength
FROM   CTE_Routine
GROUP BY RoutineType;