Thursday, October 28, 2010

[Code] IF EXISTS for procedures using INFORMATION_SCHEMA

Adam Machanic's code does two things that I love, but don't think to do.

EXEC ('CREATE PROC dbo.yourprocname AS SELECT ''stub version, to be replaced''')

  1. It uses the INFORMATION_SCHEMA tables. These are like the system tables, but are more portable (every database vendor has them and they all look the same), and are meant to be human-readable. The downside is that they don't necessarily have all the details you need. I use INFORMATION_SCHEMA.TABLES AND INFORMATION_SCHEMA.COLUMNS all the time, but forget about ROUTINES, which includes functions and stored procedures.
  2. By creating a stub entry, it ensures permissions remain, as well as other things like the original creation date. If you have an automated environment, you might do a DROP/CREATE, which would break all your explicit permissions (and reset the create_date). By doing a stub entry then an ALTER, you ensure those remain.

    Highly recommended code.

No comments: