QUERY - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-25 : 06:13:26

Hi Guys

How we find out the following things..

Table size, row size, column size, index size (both), page size

Please help me out.........

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-08-25 : 06:31:40

DBCC CHECKTABLE ('table_name')

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 06:53:39

Table and index size:
EXEC sp_spaceused N'Owner.TableName';

Data types for column size:
http://msdn.microsoft.com/en-us/library/ms187752.aspx

Page size is 8K (or 8060, ignoring the overhead)

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-08-25 : 07:02:17

Hi YellowBug

Fine

but i want the column data size in bytes...
take rows there is limit is 8060 bytes right

how to find what is the size of columns and rows in existing table.

bcoz we can't able to create index more than 900 bytes right...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-08-25 : 07:21:29

hi

try this...

SELECT DATALENGTH(COLUMN)
FROM TABLE_NAME

-------------------------
R...

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-25 : 07:22:29

To get the column data size, you need to know the data type of each column to get the storage size.
Example:
int - 4 bytes
tinyint - 1 byte

Then add up all the column sizes, and you have the row size.
There are some exceptions (BLOBs).

How many columns are in your index?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources