Technical Article

Get size in bytes of SP, View, Trigger, UDF or Rul

,

This UDF and its equivalent stored procedure will return the size of a stored procedure, view, rule or user defined function in bytes .

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE function ObSize(@s VARCHAR(255) ) 
returns int
as
BEGIN
declare @i int
set @i=(
SELECT     SUM(DATALENGTH(dbo.syscomments.text)) AS Expr1
FROM         dbo.syscomments INNER JOIN
                      dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE     (dbo.sysobjects.name =@s)
)
return @i
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_ObSize  (@s VARCHAR(255), @size int OUT ) 
AS
declare @i int
set @i=(
SELECT     SUM(DATALENGTH(dbo.syscomments.text)) AS Expr1
FROM         dbo.syscomments INNER JOIN
                      dbo.sysobjects ON dbo.syscomments.id = dbo.sysobjects.id
WHERE     (dbo.sysobjects.name =@s)
)
set @size= @i
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating