how do i get the size of a row in bytes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how do i get the size of a row in bytes

Author

Topic

waqui44
Starting Member

4 Posts

Posted - 2005-03-04 : 10:11:54

What is the fastest way to get the size rows in bytes?
I have an application that has 10 tables (1 parent, 9 dependents).
Basically what we want to do is get the amount of data inputted in the DB then compensate the employees based on the amount of data they have put in. Other that doing a query then displaying all the columns into a text field on screen then get the number of characters on it I cant find a good solution for this. Any suggestions? by the way most of my fields range from varchar (5) to text datatype.

Thank you in advance

waqui44
Starting Member

4 Posts

Posted - 2005-03-04 : 10:12:39

oh i forgot to include, I am using SQL Server 2000.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 10:20:55

Sounds like an IBM sweat shop....

How about


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE #SpaceUsed (
			  [name] 	varchar(256)
			, [rows] 	char(11)
			, [reserved] 	varchar(15)
			, [data] 	varchar(15)
			, [index_size] 	varchar(15)
			, [unused] 	varchar(15)
)
GO

DECLARE   @tablename nvarchar(256)
	, @maxtablename nvarchar(128)
	, @cmd nvarchar(1000) 
 SELECT   @tablename = ''
	, @maxtablename = MAX(name) 
   FROM   sysobjects 
  WHERE   xtype='u'

WHILE @tablename < @maxtablename 
	BEGIN
		SELECT @tablename = MIN(name) 
		  FROM sysobjects 
		 WHERE xtype='u' and name > @tablename
	 
		   SET @cmd='exec sp_spaceused['+@tablename+']'
		INSERT INTO #SpaceUsed EXEC sp_executesql @cmd
	END
SET NOCOUNT OFF
SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO

Brett

8-)

waqui44
Starting Member

4 Posts

Posted - 2005-03-04 : 10:25:20

thank X002548 I wil try it.

No were not, people are just plain slow here and we want to maximize the company's resources. We are just starting up and cant afford to get new employees. I used to do what they are doing when we just started and i was able to do 2 times what they are doing right now.

Thanks again.

waqui44
Starting Member

4 Posts

Posted - 2005-03-04 : 10:28:52

Brett,

I have used this before but and it gives me the size of the table.. I was looking for size of specific rows when i do a query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources