Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Forum Statistics

Users
Members:
1878
Members Online:
2
Guests Online:
100

Total Post History
Posts:
81448
Topics:
18714

7-Day Post History
New Posts:
1
New Topics:
0
Active Topics:
1

Our newest member
kowilmar1990z

Other

FAQ
All times are UTC [ DST ]

Google Ads

A little improvement for the SQLCop procedures

Please wait...

A little improvement for the SQLCop procedures

Postby Bitsqueezer on Thu Jan 23, 2014 3:42 pm

Hi,

yesterday I got the installation of the RedGate toolbelt which includes SQLTest to run unit tests for procedures. Really nice, and contained your SQLCop procedures (I don't know if they are outdated, couldn't find any version info).

When I tried them with my database I got errors for the decimal and the @@IDENTITY test, where in the last case some were right so I could fix that with SCOPE_IDENTITY, but the rest of the errors came from remarks where the word "decimal" was contained or also in one case a hint to not use @@IDENTITY...:-)
Also a function which loads info about columns where a column name was named "NUMERIC_..." was a reason for an incorrect error message.

So I wrote a little procedure which extracts any comment from the procedures and only need a little change in the SQLCop procedures (tested it with the mentioned two). Maybe this is useful for others who have the same problems:

  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. -- =============================================
  6. -- Author:      Christian Coppes
  7. -- Create date: 23.01.2014
  8. -- Description: Removes remarks from all procedures
  9. --              for use in test cases.
  10. --              Writes the result into temp table
  11. --              #tblOut which must be created in the calling procedure
  12. --              must look like this:
  13. --CREATE TABLE #tblOut ([object_id]     INT PRIMARY KEY,
  14. --                      [schema_id]     INT,
  15. --                      SchemaName      sysname,
  16. --                      [name]          sysname,
  17. --                      is_ms_shipped   BIT,
  18. --                      [type]          NVARCHAR(2),
  19. --                      ObjDefinition   NVARCHAR(MAX)
  20. --                    );
  21. -- =============================================
  22. CREATE PROCEDURE [SQLCop].[procRemoveRemarks]
  23. AS
  24. BEGIN
  25.     -- SET NOCOUNT ON added to prevent extra result sets from
  26.     -- interfering with SELECT statements.
  27.     SET NOCOUNT ON;
  28.     DECLARE @intObjID       INT;
  29.     DECLARE @intSchemaID    INT;
  30.     DECLARE @strName        NVARCHAR(1000);
  31.     DECLARE @strObjName     sysname;
  32.     DECLARE @strSchemaName  sysname;
  33.     DECLARE @strOut         NVARCHAR(MAX) = '';
  34.     DECLARE @intPos         INT;
  35.     DECLARE @intRow         INT;
  36.     DECLARE @intRow2        INT;
  37.     DECLARE @intLength      INT;
  38.     DECLARE @strRow         NVARCHAR(MAX);
  39.     DECLARE @bitMSShipped   BIT;
  40.     DECLARE @strType        CHAR(2);
  41.  
  42.     DECLARE @tblProc TABLE (ID_Row  INT IDENTITY (1,1) PRIMARY KEY,
  43.                             F_Row       NVARCHAR(MAX)
  44.                           );
  45.  
  46.     DECLARE tmpCursor CURSOR FAST_FORWARD READ_ONLY FOR
  47.         SELECT O.object_id, O.schema_id, O.name AS ObjectName, S.name AS SchemaName, O.is_ms_shipped, O.[type] AS ObjType FROM sys.objects AS O
  48.              INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id
  49.         WHERE OBJECT_DEFINITION(object_id) IS NOT NULL
  50.           AND type IN ('P','FN','IF','TR','TF')
  51.  
  52.     DELETE FROM #tblOut;
  53.        
  54.     OPEN tmpCursor;
  55.  
  56.     FETCH NEXT FROM tmpCursor INTO @intObjID, @intSchemaID, @strObjName, @strSchemaName, @bitMSShipped, @strType;
  57.  
  58.     WHILE (@@FETCH_STATUS = 0)
  59.     BEGIN
  60.         DELETE FROM @tblProc;
  61.  
  62.         SET @strName = @strSchemaName + '.' + @strObjName;
  63.                                    
  64.         -- sp_helptext creates a table where each record is one line of the procedure
  65.         INSERT INTO @tblProc (F_Row)
  66.             EXECUTE sp_helptext @strName;
  67.        
  68.         WHILE (SELECT COUNT(*) FROM @tblProc WHERE CHARINDEX('/*',F_Row) > 0) > 0
  69.         BEGIN
  70.             SET @intRow = 0;
  71.             SET @intRow2 = 0;
  72.  
  73.             WHILE (SELECT COUNT(*) FROM @tblProc WHERE PATINDEX('%/*%*/%',F_Row) > 0) > 0
  74.             BEGIN
  75.                 SELECT @strRow = STUFF(F_Row, CHARINDEX('/*',F_Row), CHARINDEX('*/',F_Row) + 2 - CHARINDEX('/*',F_Row),'')
  76.                   FROM @tblProc AS P
  77.                 PRINT @strRow
  78.                
  79.                 -- delete any /* ... */ comment inside of a row
  80.                 UPDATE @tblProc
  81.                    SET F_Row = STUFF(F_Row, CHARINDEX('/*',F_Row), CHARINDEX('*/',F_Row) + 2 - CHARINDEX('/*',F_Row),'')
  82.                  WHERE PATINDEX('%/*%*/%',F_Row) > 0;
  83.             END
  84.  
  85.             -- search further comment blocks over multiple rows      
  86.             SELECT @intRow = ID_Row,
  87.                    @intPos = CHARINDEX('/*',F_Row),
  88.                    @strRow = ISNULL(F_Row,'')
  89.               FROM @tblProc
  90.              WHERE CHARINDEX('/*',F_Row) > 0
  91.              ORDER BY ID_Row;
  92.  
  93.             -- if a row with a beginning comment block sign was found
  94.             IF @intRow > 0     
  95.             BEGIN
  96.                 -- remove the rest of the row which starts with /*
  97.                 UPDATE @tblProc
  98.                    SET F_Row = LEFT(F_Row,@intPos-1)
  99.                  WHERE ID_Row = @intRow;
  100.                
  101.                 -- find the corresponding comment block close sign
  102.                 SELECT TOP 1 @intRow2 = ID_Row, @intPos = CHARINDEX('*/',F_Row), @intLength = LEN(F_Row)
  103.                   FROM @tblProc
  104.                  WHERE CHARINDEX('*/',F_Row) > 0
  105.                    AND ID_Row > @intRow
  106.                  ORDER BY ID_Row;
  107.  
  108.                 IF @intRow2 > 0
  109.                 BEGIN
  110.                     IF @intLength = 2
  111.                         DELETE FROM @tblProc WHERE ID_Row = @intRow;
  112.                     ELSE
  113.                         UPDATE @tblProc
  114.                            SET F_Row = STUFF(F_Row,1,CHARINDEX('*/',F_Row)+1,'')
  115.                          WHERE ID_Row = @intRow2;
  116.  
  117.                     DELETE FROM @tblProc WHERE ID_Row > @intRow AND ID_Row < @intRow2;
  118.                 END -- IF @intRow2 > 0
  119.             END -- IF @intRow > 0
  120.            
  121.         END -- WHILE (SELECT COUNT(*) FROM @tblProc WHERE CHARINDEX('/*',F_Row) > 0) > 0
  122.  
  123.         -- delete all rows starting with "--"
  124.         DELETE FROM @tblProc
  125.          WHERE SUBSTRING(LTRIM(REPLACE(F_Row,CHAR(9),'')),1,2) = '--';
  126.  
  127.         -- remove any comments at line end
  128.         UPDATE @tblProc
  129.            SET F_Row = REVERSE(STUFF(REVERSE(F_Row),1,CHARINDEX('--',REVERSE(F_Row)) + 2,''));
  130.  
  131.         SET @strOut = '';
  132.        
  133.         SELECT @strOut = @strOut + F_Row FROM @tblProc ORDER BY ID_Row;
  134. --      PRINT @strOut;
  135.  
  136.         INSERT INTO #tblOut
  137.                 ([object_id], [schema_id], SchemaName, [name], ObjDefinition,is_ms_shipped,[type])
  138.         VALUES
  139.                 (@intObjID,
  140.                  @intSchemaID,
  141.                  @strSchemaName,
  142.                  @strObjName,
  143.                  @strOut,
  144.                  @bitMSShipped,
  145.                  @strType
  146.                  )
  147.        
  148.         FETCH NEXT FROM tmpCursor INTO @intObjID, @intSchemaID, @strObjName, @strSchemaName, @bitMSShipped, @strType;
  149.     END -- WHILE (@@FETCH_STATUS = 0)
  150.  
  151.     CLOSE tmpCursor;
  152.     DEALLOCATE tmpCursor;
  153.  
  154. --  SELECT * FROM @tblOut AS O
  155.  
  156. END


To use that the SQLCop procedures needs this change (This one also don't output an error if you have "decimal_" or "numeric_" anywhere in the procedure.):
  1. ALTER PROCEDURE [SQLCop].[test Decimal Size Problem]
  2. AS
  3. BEGIN
  4.      --Written by George Mastros
  5.      --February 25, 2012
  6.      --http://sqlcop.lessthandot.com
  7.      --http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/always-include-precision-and-scale-with
  8.    
  9.     SET NOCOUNT ON  
  10.  
  11.     DECLARE @Output NVARCHAR(max);
  12.     SET @Output = '';
  13.    
  14.  
  15.     CREATE TABLE #tblOut (  [object_id]     INT PRIMARY KEY,
  16.                             [schema_id]     INT,
  17.                             SchemaName      sysname,
  18.                             [name]          sysname,
  19.                             is_ms_shipped   BIT,
  20.                             [type]          NVARCHAR(2),
  21.                             ObjDefinition   NVARCHAR(MAX)
  22.                           );
  23.  
  24.     EXECUTE SQLCop.procRemoveRemarks;
  25.  
  26.     SELECT @Output = @Output + Schema_Name(schema_id) + '.' + name + Char(13) + Char(10)
  27.     FROM    #tblOut
  28.     WHERE   schema_id <> Schema_ID('SQLCop')
  29.             And schema_id <> Schema_Id('tSQLt')
  30.             and (
  31.             REPLACE(REPLACE(ObjDefinition, ' ', ''), 'decimal]','decimal') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%decimal[^(_]%'
  32.             Or REPLACE(REPLACE(ObjDefinition, ' ', ''), 'numeric]','numeric') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%[^i][^s]numeric[^(_]%'
  33.             )
  34.     Order By SchemaName, name  
  35.  
  36.     If @Output > ''
  37.         BEGIN
  38.             SET @Output = Char(13) + Char(10)
  39.                           + 'For more information:  '
  40.                           + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/always-include-precision-and-scale-with'
  41.                           + Char(13) + Char(10)
  42.                           + Char(13) + Char(10)
  43.                           + @Output
  44.             EXEC tSQLt.Fail @Output
  45.         END
  46.     DROP TABLE #tblOut;
  47. END;


And the second:
  1. ALTER PROCEDURE [SQLCop].[test Procedures with @@Identity]
  2. AS
  3. BEGIN
  4.     -- Written by George Mastros
  5.     -- February 25, 2012
  6.     -- http://sqlcop.lessthandot.com
  7.     -- http://wiki.lessthandot.com/index.php/6 ... tity_Value
  8.    
  9.     SET NOCOUNT ON
  10.  
  11.     Declare @Output VarChar(max)
  12.     Set @Output = ''
  13.  
  14.     CREATE TABLE #tblOut (  [object_id]     INT PRIMARY KEY,
  15.                             [schema_id]     INT,
  16.                             SchemaName      sysname,
  17.                             [name]          sysname,
  18.                             is_ms_shipped   BIT,
  19.                             [type]          NVARCHAR(2),
  20.                             ObjDefinition   NVARCHAR(MAX)
  21.                           );
  22.  
  23.     EXECUTE SQLCop.procRemoveRemarks;
  24.  
  25.     Select  @Output = @Output + Schema_Name(schema_id) + '.' + name + Char(13) + Char(10)
  26.     From    #tblOut
  27.     Where   type = 'P'
  28.             AND name Not In('sp_helpdiagrams','sp_upgraddiagrams','sp_creatediagram','testProcedures with @@Identity')
  29.             And ObjDefinition COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like '%@@identity%'
  30.             And is_ms_shipped = 0
  31.             and schema_id <> Schema_id('tSQLt')
  32.             and schema_id <> Schema_id('SQLCop')
  33.     ORDER BY Schema_Name(schema_id), name
  34.  
  35.     If @Output > ''
  36.         Begin
  37.             Set @Output = Char(13) + Char(10)
  38.                           + 'For more information:  '
  39.                           + 'http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value'
  40.                           + Char(13) + Char(10)
  41.                           + Char(13) + Char(10)
  42.                           + @Output
  43.             EXEC tSQLt.Fail @Output
  44.         End
  45.    
  46.     DROP TABLE #tblOut;
  47. END;


This was tested successfully with the RedGate SQLTest tool on a SQL Server 2008 R2. The procedure maybe needs some little adjustment if used in older SQL Server versions (like the initialization of variables which cannot be done in older versions together with declaration).

Hope that helps a little bit.

Cheers,

Christian
Bitsqueezer
Newbie
Newbie
 
Posts: 1
Joined: Thu Jan 23, 2014 3:30 pm
Unrated