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:
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.
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.