DDL triggers fire when someone changes the schema. How to write one, what EVENTDATA gives you, and how to get back in if one locks you out.
SQL SERVER – 2005 – Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename
sp_renameDB will be deprecated, so use ALTER DATABASE MODIFY NAME to rename a database in SQL Server 2005. A short script shows the new syntax.
SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
A query on sys.objects to list every stored procedure modified in last N days, or ones created lately. I use 7 days; change it to what you need.
SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC
SP_EXECUTESQL vs EXECUTE/EXEC in SQL Server 2005: what they share for dynamic SQL, like late compile and variable scope, and how they differ.
SQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
Use WITH ENCRYPTION to hide the code of a stored procedure or UDF, but keep a copy of the script. Once encrypted, there is no simple way to decrypt it.
SQL SERVER – Recompile All The Stored Procedure on Specific Table
After BCP or DTS mass inserts, stored procedures can slow down. I use sp_recompile to recompile all the stored procedure on a table, or just one procedure.
SQL SERVER – sp_HelpText for sp_HelpText – Puzzle
What happens when you run sp_HelpText on itself? It prints its own code. Plus tips for long lines, encrypted objects and searching object code.