首页 > 代码库 > REDGATE SQL TEST的使用

REDGATE SQL TEST的使用

原文:REDGATE SQL TEST的使用

REDGATE SQL TEST的使用

SQL TEST下载和破解可以参考这篇文章:http://www.cnblogs.com/VAllen/archive/2012/10/01/SQLTest.html#

SQL TEST默认已经创建好5个测试数据库中错误的存储过程

第一个存储过程测试数据库中是否有Decimal数据类型大小的问题

第二个存储过程测试数据库中是否有以SP_开头的存储过程

第三个存储过程测试数据库中使用的动态sql是否没有使用sp_executesql来调用

第四个存储过程测试数据库中的存储过程是否有@@Identity全局变量

第五个存储过程测试数据库中存储过程是否有使用SET ROWCOUNT

 

您可以编辑这些默认的测试存储过程

例如第一个存储过程,测试Decimal数据类型大小错误

 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 VarChar(max)12     Set @Output = ‘‘13   14     Select @Output = @Output + Schema_Name(schema_id) + . + name + Char(13) + Char(10)15     From    sys.objects16     WHERE    schema_id <> Schema_ID(SQLCop)17             And schema_id <> Schema_Id(tSQLt)18             and (19             REPLACE(REPLACE(Object_Definition(object_id),  , ‘‘), decimal],decimal) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE %decimal[^(]%20             Or REPLACE(REPLACE(Object_Definition(object_id),  , ‘‘), numeric],numeric) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE %[^i][^s]numeric[^(]%21             )22     Order By Schema_Name(schema_id), name  23 24     If @Output > ‘‘ 25         Begin26             Set @Output = Char(13) + Char(10) 27                           + For more information:  28                           + http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/always-include-precision-and-scale-with29                           + Char(13) + Char(10) 30                           + Char(13) + Char(10) 31                           + @Output32             EXEC tSQLt.Fail @Output33         End  34 END;

您也可以运行他,他会检查数据库中每个表的数据类型,并检查每个表中的数据

如果你想一次过执行所有的测试存储过程可以按左上角的run tests按钮


下面来试一下怎麽使用,先创建一个以SP_开头的存储过程,您可以按左上角的run tests按钮或者只选中test Procedures Named SP_

这个测试存储过程,然后右键-》run test

 

其他4个测试存储过程

 1 ALTER PROCEDURE [SQLCop].[test Procedures With SET ROWCOUNT] 2 AS 3 BEGIN 4     -- Written by George Mastros 5     -- February 25, 2012 6     -- http://sqlcop.lessthandot.com 7     -- http://sqltips.wordpress.com/2007/08/19/set-rowcount-will-not-be-supported-in-future-version-of-sql-server/ 8      9     SET NOCOUNT ON10 11     Declare @Output VarChar(max)12     Set @Output = ‘‘13   14     SELECT    @Output = @Output + Schema_Name(schema_id) + . + name + Char(13) + Char(10)15     From    sys.all_objects16     Where    type = P17             AND name Not In(sp_helpdiagrams,sp_upgraddiagrams,sp_creatediagram,testProcedures With SET ROWCOUNT)18             And Replace(Object_Definition(Object_id),  , ‘‘) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like %SETROWCOUNT%19             And is_ms_shipped = 020             and schema_id <> Schema_id(tSQLt)21             and schema_id <> Schema_id(SQLCop)            22     ORDER BY Schema_Name(schema_id) + . + name23 24     If @Output > ‘‘ 25         Begin26             Set @Output = Char(13) + Char(10) 27                           + For more information:  28                           + http://sqltips.wordpress.com/2007/08/19/set-rowcount-will-not-be-supported-in-future-version-of-sql-server/29                           + Char(13) + Char(10) 30                           + Char(13) + Char(10) 31                           + @Output32             EXEC tSQLt.Fail @Output33         End34 END;
View Code
 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_Different_Ways_To_Get_The_Current_Identity_Value 8      9     SET NOCOUNT ON10 11     Declare @Output VarChar(max)12     Set @Output = ‘‘13 14     Select    @Output = @Output + Schema_Name(schema_id) + . + name + Char(13) + Char(10)15     From    sys.all_objects16     Where    type = P17             AND name Not In(sp_helpdiagrams,sp_upgraddiagrams,sp_creatediagram,testProcedures with @@Identity)18             And Object_Definition(object_id) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like %@@identity%19             And is_ms_shipped = 020             and schema_id <> Schema_id(tSQLt)21             and schema_id <> Schema_id(SQLCop)22     ORDER BY Schema_Name(schema_id), name 23 24     If @Output > ‘‘ 25         Begin26             Set @Output = Char(13) + Char(10) 27                           + For more information:  28                           + http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value29                           + Char(13) + Char(10) 30                           + Char(13) + Char(10) 31                           + @Output32             EXEC tSQLt.Fail @Output33         End34     35 END;
View Code
 1 ALTER PROCEDURE [SQLCop].[test Procedures using dynamic SQL without sp_executesql] 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/DataDesign/avoid-conversions-in-execution-plans-by- 8      9     SET NOCOUNT ON10     11     Declare @Output VarChar(max)12     Set @Output = ‘‘13 14     SELECT    @Output = @Output + SCHEMA_NAME(so.uid) + . + so.name + Char(13) + Char(10)15     From    sys.sql_modules sm16             Inner Join sys.sysobjects so17                 On  sm.object_id = so.id18                 And so.type = P19     Where    so.uid <> Schema_Id(tSQLt)20             And so.uid <> Schema_Id(SQLCop)21             And Replace(sm.definition,  , ‘‘) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like %Exec(%22             And Replace(sm.definition,  , ‘‘) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Not Like %sp_Executesql%23             And OBJECTPROPERTY(so.id, NIsMSShipped) = 024     Order By SCHEMA_NAME(so.uid),so.name25 26     If @Output > ‘‘ 27         Begin28             Set @Output = Char(13) + Char(10) 29                           + For more information:  30                           + http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by-31                           + Char(13) + Char(10) 32                           + Char(13) + Char(10) 33                           + @Output34             EXEC tSQLt.Fail @Output35         End36  37 END;
View Code
 1 ALTER PROCEDURE [SQLCop].[test Procedures Named SP_] 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/MSSQLServer/don-t-start-your-procedures-with-sp_ 8      9     SET NOCOUNT ON10     11     Declare @Output VarChar(max)12     Set @Output = ‘‘13   14     SELECT    @Output = @Output + SPECIFIC_SCHEMA + . + SPECIFIC_NAME + Char(13) + Char(10)15     From    INFORMATION_SCHEMA.ROUTINES16     Where    SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE sp[_]%17             And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE %diagram%18             AND ROUTINE_SCHEMA <> tSQLt19     Order By SPECIFIC_SCHEMA,SPECIFIC_NAME20 21     If @Output > ‘‘ 22         Begin23             Set @Output = Char(13) + Char(10) 24                           + For more information:  25                           + http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_26                           + Char(13) + Char(10) 27                           + Char(13) + Char(10) 28                           + @Output29             EXEC tSQLt.Fail @Output30         End 31 END;
View Code

还会在测试数据库生成一些存储过程和函数

某些存储过程还加密了的


创建测试存储过程

SQL TEST跟SQL PROMPT一样,根据SQLSERVER版本来开发的