andyfrench.info

If you want to count the number of lines of SQL in stored procedures and functions try running the following SQL:

USE [Database name here]

SELECT  t.sp_name,
        SUM(t.lines_of_code) - 1 AS lines_ofcode,
        t.type_desc
FROM    ( SELECT    o.name AS sp_name,
                    ( LEN(c.text) - LEN(REPLACE(c.text, CHAR(10), '')) ) AS lines_of_code,
                    CASE WHEN o.xtype = 'P' THEN 'Stored Procedure'
                         WHEN o.xtype IN ( 'FN', 'IF', 'TF' ) THEN 'Function'
                    END AS type_desc
          FROM      sysobjects o
                    INNER JOIN syscomments c ON c.id = o.id
          WHERE     o.xtype IN ( 'P', 'FN', 'IF', 'TF' )
                    AND o.category = 0
                    AND o.name NOT IN ( 'fn_diagramobjects', 'sp_alterdiagram',
                                        'sp_creatediagram', 'sp_dropdiagram',
                                        'sp_helpdiagramdefinition',
                                        'sp_helpdiagrams', 'sp_renamediagram',
                                        'sp_upgraddiagrams', 'sysdiagrams' )
        ) t
GROUP BY t.sp_name,
        t.type_desc
ORDER BY 1

The following rules might help out in protecting a site against SQL injection:

[ISAPI_Rewrite]
RewriteRule .*(?:global.asa|default\.ida|root\.exe|\.\.).* . [F,I,O]

RewriteRule .*(?:DECLARE).* /null.htm [F,I]

RewriteRule ^.*\+update\+.*$ /null.htm [F,I]
RewriteRule ^.*SUBSTRING\(.*$ /null.htm [F,I]
RewriteRule ^.*CHARINDEX.*$ /null.htm [F,I]
RewriteRule ^.*NVARCHAR.*$ /null.htm [F,I]
RewriteRule ^.*CHAR\(.*$ /null.htm [F,I]
RewriteRule ^.*CAST\(.*$ /null.htm [F,I]
RewriteRule ^.*%20xp_.*$ /null.htm [F,I]
RewriteRule ^.*%20@.*$ /null.htm [F,I]
RewriteRule ^.*@%20.*$ /null.htm [F,I]
RewriteRule ^.*';*$ /null.htm [F,I]
RewriteRule ^.*EXEC\(@.*$ /null.htm [F,I]
RewriteRule ^.*sp_password.*$ /null.htm [F,I]

F – forbidden
I – Ignore case

Setting NOCOUNT to ON stops the message that shows the count of the number of rows affected by a T-SQL statement or stored procedure from being returned as part of the result set.

Why is this useful or important? It can result in a significant performance boost when running SQL statements:

“When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

The setting specified by SET NOCOUNT is in effect at execute or run time and not at parse time.” – from SQL Server online documentation