Sometimes we need to disable triggers to perform some tasks. Disabling the trigger by going to each table is very tedious. Following is avery quick way of disabling all triggers on all tables of the given database in a single statement.
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
To enable all triggers, you can use following statement
sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”
Remember good old days of DOS when you were able to execute any file by adding it autoexec.bat file? What if you need to perform some processing every time your SQL Server starts? The answer is sp_procoption By using this system stored procedure you can schedule a stored procedure to be executed every time your SQL Server get restarted.
Please keep in mind that the stored procedure:
To use sp_procoption, you must be a member of sysadmin role.
Following is an example to schedule a simple stored procedure which increment a counter to give you an idea how many times your SQL Serever has been restarted.
Use master
Go
–Create a test table in master database
Create table testTable (startupCount int)
–Create a stored procedure to schedule at startup
Create proc usp_IncrementStartupCount
as
Begin
If exists (Select top 1 * from testTable)
Begin
Update testTable Set startupCount=startupCount+1;
End
Else
Begin
Insert into testTable values (1);
End
End
–Schedule the stored procedure for startup
exec sp_procoption ‘usp_IncrementStartupCount’,’startup’,‘on’
———-STOP—————
—RESTART the SQL SERVER —
———-STOP—————
–After restarting, execute the following query
Select * from testTable
There are lots of shortcuts in SQL Server Management Studio and I found one yesterday. May be most of you are already aware of this but I just want to share it.
If you want to execute a stored procedure and assuming it does not require any parameter or all parameters have default values assigned then instead of typing exec before the stored procedure name, you can just select it and press Ctrl+E or F5 and that is it!
For example, we have a following stored procedure:
Create Proc GetServerDateTime
as
Begin
Select GetDate()
End
Just highlight the name of stored procedure which is GetServerDateTime and press F5. SMS will execute it for you without asking you to type exec statement.
