RedGate 工具SQLTEST 1.0.15.1

2022-04-23 16:16:21

原文:RedGate 工具SQLTEST 1.0.15.1

RedGate 工具SQLTEST 1.0.15.1

SQL TEST1.0.15.1的破解可以参考这篇文章：http://www.cnblogs.com/VAllen/archive/2012/10/01/SQLTest.html

SQL TEST1.0.15.1下载：http://www.kuaipan.cn/file/id_4401224786926114.htm

安装完SQL TEST之后，会在SSMS了的工具栏见到SQL TEST的按钮

点击按钮就会弹出SQL TEST

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

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

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

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

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

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

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

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

 ALTER PROCEDURE [SQLCop].[test Decimal Size Problem]

 AS

 BEGIN

     -- Written by George Mastros

     -- February 25, 2012

     -- http://sqlcop.lessthandot.com

     -- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/always-include-precision-and-scale-with

     SET NOCOUNT ON  

     Declare @Output VarChar(max)

     Set @Output = ''

     Select @Output = @Output + Schema_Name(schema_id) + '.' + name + Char(13) + Char(10)

     From    sys.objects

     WHERE    schema_id <> Schema_ID('SQLCop')

             And schema_id <> Schema_Id('tSQLt')

             and (

             REPLACE(REPLACE(Object_Definition(object_id), ' ', ''), 'decimal]','decimal') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%decimal[^(]%'

             Or REPLACE(REPLACE(Object_Definition(object_id), ' ', ''), 'numeric]','numeric') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE '%[^i][^s]numeric[^(]%'

             )

     Order By Schema_Name(schema_id), name  

     If @Output > ''

         Begin

             Set @Output = Char(13) + Char(10)

                           + 'For more information:  '

                           + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/always-include-precision-and-scale-with'

                           + Char(13) + Char(10)

                           + Char(13) + Char(10)

                           + @Output

             EXEC tSQLt.Fail @Output

         End

 END;

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

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

下面来试一下怎麽使用，先创建一个以SP_开头的存储过程

您可以按左上角的Run Tests按钮或者只选中test Procedures Named SP_这个测试存储过程

然后右键-》Run Test

其他4个测试存储过程

 ALTER PROCEDURE [SQLCop].[test Procedures With SET ROWCOUNT]

 AS

 BEGIN

     -- Written by George Mastros

     -- February 25, 2012

     -- http://sqlcop.lessthandot.com

     -- http://sqltips.wordpress.com/2007/08/19/set-rowcount-will-not-be-supported-in-future-version-of-sql-server/

     SET NOCOUNT ON

     Declare @Output VarChar(max)

     Set @Output = ''

     SELECT    @Output = @Output + Schema_Name(schema_id) + '.' + name + Char(13) + Char(10)

     From    sys.all_objects

     Where    type = 'P'

             AND name Not In('sp_helpdiagrams','sp_upgraddiagrams','sp_creatediagram','testProcedures With SET ROWCOUNT')

             And Replace(Object_Definition(Object_id), ' ', '') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like '%SETROWCOUNT%'

             And is_ms_shipped = 0

             and schema_id <> Schema_id('tSQLt')

             and schema_id <> Schema_id('SQLCop')

     ORDER BY Schema_Name(schema_id) + '.' + name

     If @Output > ''

         Begin

             Set @Output = Char(13) + Char(10)

                           + 'For more information:  '

                           + 'http://sqltips.wordpress.com/2007/08/19/set-rowcount-will-not-be-supported-in-future-version-of-sql-server/'

                           + Char(13) + Char(10)

                           + Char(13) + Char(10)

                           + @Output

             EXEC tSQLt.Fail @Output

         End

 END;

 ALTER PROCEDURE [SQLCop].[test Procedures with @@Identity]

 AS

 BEGIN

     -- Written by George Mastros

     -- February 25, 2012

     -- http://sqlcop.lessthandot.com

     -- http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value

     SET NOCOUNT ON

     Declare @Output VarChar(max)

     Set @Output = ''

     Select    @Output = @Output + Schema_Name(schema_id) + '.' + name + Char(13) + Char(10)

     From    sys.all_objects

     Where    type = 'P'

             AND name Not In('sp_helpdiagrams','sp_upgraddiagrams','sp_creatediagram','testProcedures with @@Identity')

             And Object_Definition(object_id) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like '%@@identity%'

             And is_ms_shipped = 0

             and schema_id <> Schema_id('tSQLt')

             and schema_id <> Schema_id('SQLCop')

     ORDER BY Schema_Name(schema_id), name 

     If @Output > ''

         Begin

             Set @Output = Char(13) + Char(10)

                           + 'For more information:  '

                           + 'http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value'

                           + Char(13) + Char(10)

                           + Char(13) + Char(10)

                           + @Output

             EXEC tSQLt.Fail @Output

         End

 END;

 ALTER PROCEDURE [SQLCop].[test Procedures using dynamic SQL without sp_executesql]

 AS

 BEGIN

     -- Written by George Mastros

     -- February 25, 2012

     -- http://sqlcop.lessthandot.com

     -- http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by-

     SET NOCOUNT ON

     Declare @Output VarChar(max)

     Set @Output = ''

     SELECT    @Output = @Output + SCHEMA_NAME(so.uid) + '.' + so.name + Char(13) + Char(10)

     From    sys.sql_modules sm

             Inner Join sys.sysobjects so

                 On  sm.object_id = so.id

                 And so.type = 'P'

     Where    so.uid <> Schema_Id('tSQLt')

             And so.uid <> Schema_Id('SQLCop')

             And Replace(sm.definition, ' ', '') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Like '%Exec(%'

             And Replace(sm.definition, ' ', '') COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI Not Like '%sp_Executesql%'

             And OBJECTPROPERTY(so.id, N'IsMSShipped') = 0

     Order By SCHEMA_NAME(so.uid),so.name

     If @Output > ''

         Begin

             Set @Output = Char(13) + Char(10)

                           + 'For more information:  '

                           + 'http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/avoid-conversions-in-execution-plans-by-'

                           + Char(13) + Char(10)

                           + Char(13) + Char(10)

                           + @Output

             EXEC tSQLt.Fail @Output

         End

 END;

 ALTER PROCEDURE [SQLCop].[test Procedures Named SP_]

 AS

 BEGIN

     -- Written by George Mastros

     -- February 25, 2012

     -- http://sqlcop.lessthandot.com

     -- http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_

     SET NOCOUNT ON

     Declare @Output VarChar(max)

     Set @Output = ''

     SELECT    @Output = @Output + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + Char(13) + Char(10)

     From    INFORMATION_SCHEMA.ROUTINES

     Where    SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI LIKE 'sp[_]%'

             And SPECIFIC_NAME COLLATE SQL_LATIN1_GENERAL_CP1_CI_AI NOT LIKE '%diagram%'

             AND ROUTINE_SCHEMA <> 'tSQLt'

     Order By SPECIFIC_SCHEMA,SPECIFIC_NAME

     If @Output > ''

         Begin

             Set @Output = Char(13) + Char(10)

                           + 'For more information:  '

                           + 'http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/don-t-start-your-procedures-with-sp_'

                           + Char(13) + Char(10)

                           + Char(13) + Char(10)

                           + @Output

             EXEC tSQLt.Fail @Output

         End

 END;