如何计算SQL Server 2005中存储过程的SIZE？

I was asked for a comprehensive breakdown on space used within a specific database. I know I can use sys.dm_db_partition_stats in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the individual and total size of the stored procedures in a database? (Short of opening each one and counting the characters, of course.)

我被要求对特定数据库中使用的空间进行全面细分。我知道我可以在SQL Server 2005中使用sys.dm_db_partition_stats来计算数据库中每个表使用多少空间,但有没有办法确定数据库中存储过程的个别和总大小? (当然,没有打开每一个并计算字符数。)

Total space used by stored procs is not likely to be significant (compared to actual data), but with hundreds of them, it could add up.

存储过程使用的总空间不太可能很大(与实际数据相比),但有数百个,它可能会相加。

4 个解决方案

#1

;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

#2

A slightly better way than counting the characters, is to use information schema.routines. You could sum the length of each Routine Definition as (Note each routine definition will max out at 4,000 characters, see below for a method that doesn't have this restriction):

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

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

Or you could return the length of each sp

或者你可以返回每个sp的长度

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

It's unlikely that the length of your stored procedures is the problem. Usually running out of space with a database is due to things like not backing up the log file (and then shrinking it using dbcc shrinkfile or dbcc shrinkdatabase).

存储过程的长度不太可能是问题所在。通常用数据库耗尽空间是因为没有备份日志文件(然后使用dbcc shrinkfile或dbcc shrinkdatabase缩小它)。

In Sql 2000, here is a routine that would provide the length without the 4000 character limit of above:

在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

#3

Dave_H's solution hits a limit of 4,000 character in the information_schema.routines table

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

Try this, first you generate the a table with the full text of the sprocs, then sum the character lengths.

试试这个,首先生成一个包含sprocs全文的表,然后对字符长度求和。

--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

#4

Need to use DATALENGTH rather than LEN to get the number of bytes rather than the number of characters because the definition column of the sys.sql_modules catalogue view is NVARCHAR(MAX) i.e. Unicode

需要使用DATALENGTH而不是LEN来获取字节数而不是字符数,因为sys.sql_modules目录视图的定义列是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

#1

;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

#2

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