This post is second part of my previous post about SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
SQL SERVER – 2005 – Server and Database Level DDL Triggers Examples and Explanation
Let’s learn about Server and Database Level DDL Triggers Examples and Explanation here. Let us learn more about this topic.
SQL SERVER – 2005 – Use ALTER DATABASE MODIFY NAME Instead of sp_renameDB to rename
To rename database it is very common to use for SQL Server 2000 user : EXEC sp_renameDB 'oldDB','newDB' sp_renameDB syntax will be deprecated in the future version of SQL Server. It is supported in SQL Server 2005 for backwards compatibility only. It is recommended to use ALTER DATABASE MODIFY NAME…
Read MoreSQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days
I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure.…
Read MoreSQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC
Common Properties of SP_EXECUTESQL and EXECUTE/EXEC The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are…
Read MoreSQL SERVER – Explanation of WITH ENCRYPTION clause for Stored Procedure and User Defined Functions
This article is written to answer following two questions I have received in last one week. Questions 1) How to hide code of my Stored Procedure that no one can see it? 2) Our DBA has left the job and one of the function which retrieves important information is encrypted,…
Read MoreSQL SERVER – Recompile All The Stored Procedure on Specific Table
I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles…
Read More