Disable a trigger temporarily.

In my project I was thinking is it possible to disable all triggers temporarily?

Then answer is yes. We can disable it. The alternate is we can drop them and re-create it but it's not good way.

Any how to disable trigger it's not good idea because it can create a lot more issue with the data integrity. But you can perform this action when you alone are working on a database or in test environment or bulk inserting data.

But make sure once you have done your work enable all the trigger so you can maintain data integrity.

While you are importing bulk data then trigger should not fire once it's done then you have to fix to fire it again when actual data is coming.

Whenever I am doing this thing then I am disabling all the trigger using following command.

To disable all constraints and trigger:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

To enable all constraints and trigger:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER

Find Stored Procedure name with help of partial name (word).

Some times we want to find a stored procedure but don’t remember exactly sp name then it’s bit complex to find the whole name of the sp in Management Studio. But might be you know that SQL Server provides some system object from there we can easily finds the any object name. There are inbuilt tables which stores the all the details of table, view, sp, triggers, comment etc….

Like there is one table name sys.procedure which has all the information about available stored procedures.

If we want to find all the sp which starts name ‘adm’ then just write below qyery

select * from sys.procedures where name like 'adm%'

Like SP we can also find details about all the available tables in SQL Server.

select * from sys.tables where name like 'adm%'

above query will returns all the tables which starts with adm so we can use like clause as we are using in normal table.

Find out the used space for a Table in SQL Server

Some time we would like to know how much space table is using to store the data on a disk.

So SQL Server has a built in SP to find out the used space by table.

sp_spaceused '‘Tablename'

once you will execute above stored procedure you will see the following result.



Actually it is very useful that how much amount of space data is occuping on the disk.


If you have a no of tables in database and you don't want to execute sp one by one then here is
the stored procedure which will retrieve all the talbe name from the system object and then will
pass one by one as a parameter to previous described sp and display the result on the screen at shot

Here is the code for SP

CREATE PROCEDURE [dbo].[dms_FindAllTableUsedSpace]

AS

DECLARE @TName VARCHAR(100)

--Declare cursor to find available tables in system objects

DECLARE curtablelist CURSOR

FOR

select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

-- Create a temp table to hold the table name temporarily and then loop through all the table and run sp with that table name

CREATE TABLE #TableListTemp

(

TName varchar(100), NofR varchar(100), ResSize varchar(50), DataSize varchar(50), IndexSize varchar(50), FreeSize varchar(50)

)

-- We are opening cursor here

OPEN curtablelist

--Get the first table name from the cursor

FETCH NEXT FROM curtablelist INTO @TName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TableListTemp

EXEC sp_spaceused @TName

--Get the next table name

FETCH NEXT FROM curtablelist INTO @TName

END

--now close the cursor

CLOSE curtablelist

-- deallocate the used memory by cursor

DEALLOCATE curtablelist

--retrieve all the records from the temp table

SELECT * FROM #TableListTemp

--Drop the temp table free up the memory

DROP TABLE #TableListTemp

Here is the code to execute the SP

EXEC dms_FindAllTableUsedSpace

Mouse events on Transparent Element

Recently I am working on a project named 2011.

I am working on a Document Manager Workflow Module, in that project i need to render a div element on a image element to draw some annotation. So what I wanted is to draw a div on image with transparent background and then catch a mouse click event on that div.

I were trying to achieve that since last 3 days but I couldn't. If I am setting background color or background image than I were able to trap that mouse event in IE but if I am setting transparent background than I weren't able to trap any mouse event.

After that googling but not get any solution or might be I were not able to search it properly.

So finally I were make some changes in my code logically, and at the end I got succedd.

What I have done is set transparent background then set a transparent image as a background of that div.

How it is working?

I need transparent background so I set transparent background using style attributes and then I overlap transparent image on that so that transparent image is able to trap mouse event

or simple way

I need transparent background so I set background with transparent image so that transparent image is able to trap mouse event

Here is the faulty code

<img src="yourimagepath" alt='' />

<div style="position:absolute;top:0px;left:0px;padding:10px;width:128px;height:128px;background-color:Transparent;" onclick="alert('you clicked me');">

</div>

Here you will not be able to trap onclick event of the div

Here is the final and working code

<img src="yourimagepath" alt='' />

<div style="position:absolute;top:0px;left:0px;padding:10px;width:128px;height:128px;background-image:url(yourtransparentimagepath);" onclick="alert('you clicked me');">

</div>

Here you will be able to trap onclick event of the div

* Note change image source with your image file path.