I have simple product, ApexSQL Clean, that analyzes unreferenced objects in database but we are going to rebuild it to improve the reference algorithm and add a lot of new "cleaning" features. Here is a short list. Can anybody think of anything else? Are these good items? Any feedback would be greatly appreciated The most useful things in ApexSQL Clean, I think, would be following: 1. Recovering true dependences into "sysdepends" 2. Showing for possible dropping unused objects, datatypes, users, roles 3. Showing for possible dropping empty files and filegroups 4. Showing for possible dropping duplicated indexes and fk 5. Removing superfluous permissions (user and its role have the rights - it is dubbing; user doesn't work with a table but has the right; user has both transitive and direct rights) 6. Renaming constraints into sensible names (FK_8927346594 -> FK_Table1_Col1_ref_Table2_Col5) 7. Analising sizes of blob fields and configuring "text in row" if the case is favourable 8. "Smoothing" collations of columns as the database default collation and "smoothing" all collations in a database as the server default collation 9. Corrections user-login links (useful when a database has been attached or restored from another sql-server), removing "lost" users 10. Showing for possible dropping roles/users that do not have Brian Lockwood ApexSQL Software http://www.apexsql.com Value Added SQL Server Tools
How about removing logins that have no access to any databases and are part of no roles. ie not sysadmin or not setupadmin etc? By the way I have TSQL code to do this if you're interested.
thx! we'll add this to the list. Brian Lockwood ApexSQL Software http://www.apexsql.com Value Added SQL Server Tools
This might be more troublesome, how about identifing unused indexes and drop them. Also, how about dropping unused column statistics, assuming they could be identified. How about unused jobs? How about identifying tables with no data in them (maybe this overlaps what has already been suggested?) ----------------------------- Brad M. McGehee, MVP Webmaster SQL-Server-Performance.Com
