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.