首页 > 代码库 > REDGATE SQL TEST的使用
REDGATE SQL TEST的使用
原文:REDGATE SQL TEST的使用
View Code View Code View Code View Code
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-with‘29 + 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 = ‘P‘17 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;
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 = ‘P‘17 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_Value‘29 + Char(13) + Char(10) 30 + Char(13) + Char(10) 31 + @Output32 EXEC tSQLt.Fail @Output33 End34 35 END;
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 = ‘P‘19 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, N‘IsMSShipped‘) = 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;
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 <> ‘tSQLt‘19 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;
还会在测试数据库生成一些存储过程和函数
某些存储过程还加密了的
创建测试存储过程
SQL TEST跟SQL PROMPT一样,根据SQLSERVER版本来开发的
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。