Sunday, May 27, 2007

Scripting T-SQL

Sql Server displays the creation date for db objects, but does not show the last modified date. That makes it hard to tell if and when an object like a view, sp, or UDF was last changed.

For that reason, I always write my script and save it to a disk file, using the following general structure:

DROP PROC dbo.MyProcedure
GO

CREATE PROC dbo.MyProcedure
AS
-- actual code goes here
GO
GRANT EXECUTE ON dbo.MyProcedure TO [MyUserName]
GO

This has two benefits:
1. I can run the script on the dev system to verify it works, and make any needed changes
2. When I run the final script on the production system, the creation date will actually represent the date last modified

IMPORTANT: You cannot use this method for tables, since you will lose all the data therein!

No comments: