Bookmark : Listing Common SQL code smells

A great list of things we love to hate
Listing Common SQL code smells

Updating Statistics Manually

To update statistics manually -

exec sp_updatestats

Without parameters this updates all statistics in current database.

Run in all databases like this -

exec sp_MSForeachdb 'use [?];exec sp_updatestats'

Indexed views

sp_updatestats does not update indexed view stats

SQL login passwords and Pwdcompare

I'll spare the details but after a a little investigation, the HASHBYTES function cannot be used to generate a SQL login password. There are 2 undocumented functions pwdencrypt and pwdcompare that Management Studio uses for this.

If you were really interested in how to form the passwords, then the login migration script here will give more insight.

Link : How to transfer the logins and the passwords between instances of SQL Server 2005/8

The use of pwdcompare can be demonstrated as follows -

If I was daft enough to use my cat's name as a password (I'm not), the following query would return the username I had used the password with.

select name from sys.syslogins where pwdcompare('coco', password) = 1

Links:
cannot engineer sql password hash using HASHBYTES, have to use the undocumented stored procedure 'pwdencrypt'
SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt

Bookmark : WhoIsActive 10.0 / WhoIsActiveUI

Adam Machanic’s FREE WhoIsActive script has reached version 10.0

Download : WhoIsActive 10.0

and SchemaSolutions have produced a FREE SSMS plugin for it, called WhoIsActiveUI .

Download : WhoIsActiveUI

r

Bookmark : Project Lucy

Quest Software have launched Project Lucy to analyse SQL Profiler trace (.trc) files.

Get their opinion on your servers for free at https://www.projectlucy.com/

MCM Program changes

Most announcements from PASS reaching me via twitter are about Denali, but this news re; the MCM programme is exciting and makes it far more accessable to the majority.

Changes to the Microsoft Certified Master of SQL Server Program

SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos 

Amazon : Official SQL Server 2008 Certified Master Readiness List

Bookmark : Naming standardization scripts

Michael Søndergaard has written some excellent Naming standardization scripts that he has published on his site.

They cover the naming of Indexes , Check Constraints, Default Constraints and Foreign Keys and can potentially save you a lot of time.
This of course is only if you can agree a Naming convention with your colleagues...

r

dbautils.spFixIndexNaming
dbautils.spFixColumnCheckNaming
dbautils.spFixColumnDefaultNaming
dbautils.spFixForeignKeyNaming