Review Database objects using SQL COP

Review Database

    There are lots of tool for static code analysis available. so static code analysis perform by the most of application developer. And each application have a database or number of databases associated.

But when the code analysis tasks comes for database code , generally developer require to see each database object manually or by using information_schema & sys objects of database server. the lots of manual work require to perform to code analyse for sql server/database(s) .



SQL COP provide a interface to find all common issue with a database. It's free tool from lessthandot.com IT Community's personals. they provide an executable to review your database. SQL COP is very useful tool for a manager/team lead to review the common issues in the database design / T-Sql.

Common Issues in database design /T-SQL:

    The issue in the Sql Server database can be cause design/T-Sql code/Server setting.

• Decimal size problem - default size for decimal is precision=18 & scale =0 , so scale issue 
• Procedure with dynamic queries - open to sql injection
• Procedure with dynamic queries without sp_executesql - with exec
• Procedure with SP_  - sp_ preserve sql server stored procedure
• Procedure with SET NOCOUNT ON  - decrease performance
• Procedure with SET ROWCOUNT  - decrease performance
• Procedure with @@identity   - invalid identity value no scope defined
• Undocumented procedure    - maintenance issue 
• varchar/char size problems - default size is 1
• Tbl table prefix 
• Table name problem - special character in table name cause issue in queries sometime
• Missing Foreign Keys - relationship / referential problem
• Missing a Primary Key - performance issue
• Unnamed constraints  - unable to identify constraints in performance tuning
• Empty table - no use in application
• Fragmented indexes - outdated index
• Database collation  - different collation on different database object causes issue in queries

  Installing SQL COP:

Download SQL COP from lessthandot.com IT Community portal. or from MyWorkSpace https://drive.google.com/file/d/0B9vkinxiudBGVXE3N0FGNDYtckU/view?usp=sharing

Size: 428KB , It's standalone executable.Just double click & use.

How to use:

 double click on the executable. the interface will open., Fill all detail(server/database/authentication type/user/password) for database to whom you wants to review.

On connect , It comes up with all common issue in database code/design as in sql cop.and keep on scrolling down for all issues.

Behind it :

There are lots of queries on information_schema & sys objects of sql server for a database which evaluate all the result in efficient way.

Summary: 

SQL COP can make your life easy. It's excellent tool for review database in a glance .It will helps you to perform checks on database for performance and standard of sql server database.



Reference :

lessthandot.com

~

Sunit Kanyan

technocrats@sunitkanyan.in