August 30, 2012 at 9:40 am
Using SQL Server 2005, no access to reports. Need to determine how big a row of data is in a table of 400+ columns. Can't remember what systems views to use to help determine this. Any help while I keep digging is greatly appreciated.
August 30, 2012 at 9:48 am
Information_schema.Columns has the datatypes and sizes. The code I use is below, but it won't give you sizes for bits, ints, etc. You have to remember the whole Money is 8 bytes, stuff.
SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name, Is_Nullable, Column_Default,
Data_Type, Character_Maximum_Length as ChLen, numeric_Precision, Numeric_Scale,
Datetime_Precision
FROM Information_Schema.columns
WHERE Table_name = <MyTable>
ORDER BY Table_Name;
August 30, 2012 at 10:05 am
Brandie Tarvin (8/30/2012)
Information_schema.Columns has the datatypes and sizes. The code I use is below, but it won't give you sizes for bits, ints, etc. You have to remember the whole Money is 8 bytes, stuff.
SELECT Table_Catalog, Table_Schema, Table_Name, Column_Name, Is_Nullable, Column_Default,
Data_Type, Character_Maximum_Length as ChLen, numeric_Precision, Numeric_Scale,
Datetime_Precision
FROM Information_Schema.columns
WHERE Table_name = <MyTable>
ORDER BY Table_Name;
Thanks Brandie, but I quess I wasn't as clear as I thought. I have one row of data inserted into this table, I'm trying to figure out how much space the data is using. We are having an issue with replicating data with this particular table using Data Extend (the replication app being used in theater).
I could just write some code to write some code to calculate the size, but I would have sworn there were some DMV's or such that would make this easier.
August 30, 2012 at 10:10 am
EDIT: Lynn, I see index and session and task related DMVs, but none actually related to seeing the size of a row. I assume you don't have access to the GUI? Because there are reports in SSMS that show table sizes.
--------------------------------------
Here's what I've got so far for the variable stuff and nullability sizes. I haven't yet gone through and calculated the fixed length stuff.
SELECT 2 + ((NumCols + 7) / 8) AS ColNullabilitySize
FROM (SELECT COUNT(Column_Name) AS NumCols
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable' AND Is_Nullable = 'YES') a
SELECT SUM(2 + (ISNULL(NumCols,0) * 2) + ISNULL(ChLen,0)) AS ColVarSize
FROM (SELECT COUNT(Column_Name) AS NumCols, Character_Maximum_Length AS ChLen
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable'
AND (Data_Type LIKE 'VAR%' or Data_Type LIKE 'NVAR%')
GROUP BY Character_Maximum_Length) b
SELECT SUM(CASE WHEN numeric_Precision IN (1-9) THEN 5
WHEN numeric_Precision IN (10-19) THEN 9
WHEN numeric_Precision IN (20-28) THEN 13
WHEN numeric_Precision IN (29-38) THEN 17 ELSE 0 END) AS ColPrecisionSize
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable' AND Data_Type IN ('DECIMAL','FLOAT','NUMERIC');
EDIT AGAIN: I came up with the fixed length stuff (I've been needing something like this for our environment, which is why I'm willing to do this coding). Let me know if this looks realistic to you or not.
SELECT SUM(CASE WHEN Data_Type IN ('INT','REAL','SMALLMONEY','SMALLDATETIME') THEN 4
WHEN Data_Type IN ('BIGINT','DATETIME','MONEY') THEN 8
WHEN Data_Type IN ('SMALLINT') THEN 2
WHEN Data_Type IN ('TINYINT','BIT') THEN 1
WHEN Data_Type IN ('CHAR','BINARY') THEN Character_Maximum_Length
WHEN Data_Type LIKE '%MAX%' OR Data_Type IN ('TEXT','IMAGE') THEN 2147483647
WHEN Data_Type IN ('NTEXT') THEN 2147483646
ELSE 0
END) AS ColFixedSize
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable'
AND (Data_Type IN ('INT','REAL','SMALLMONEY','SMALLDATETIME','BIGINT','DATETIME','MONEY',
'SMALLINT','TINYINT','BIT','CHAR','BINARY','TEXT','IMAGE','NTEXT')
OR Data_Type LIKE '%MAX%');
August 30, 2012 at 10:32 am
Posting because I edited the last reply instead of adding a new one so you'd get a notification...
August 30, 2012 at 10:35 am
Brandie Tarvin (8/30/2012)
EDIT: Lynn, I see index and session and task related DMVs, but none actually related to seeing the size of a row. I assume you don't have access to the GUI? Because there are reports in SSMS that show table sizes.--------------------------------------
Here's what I've got so far for the variable stuff and nullability sizes. I haven't yet gone through and calculated the fixed length stuff.
SELECT 2 + ((NumCols + 7) / 8) AS ColNullabilitySize
FROM (SELECT COUNT(Column_Name) AS NumCols
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable' AND Is_Nullable = 'YES') a
SELECT SUM(2 + (ISNULL(NumCols,0) * 2) + ISNULL(ChLen,0)) AS ColVarSize
FROM (SELECT COUNT(Column_Name) AS NumCols, Character_Maximum_Length AS ChLen
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable'
AND (Data_Type LIKE 'VAR%' or Data_Type LIKE 'NVAR%')
GROUP BY Character_Maximum_Length) b
SELECT SUM(CASE WHEN numeric_Precision IN (1-9) THEN 5
WHEN numeric_Precision IN (10-19) THEN 9
WHEN numeric_Precision IN (20-28) THEN 13
WHEN numeric_Precision IN (29-38) THEN 17 ELSE 0 END) AS ColPrecisionSize
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable' AND Data_Type IN ('DECIMAL','FLOAT','NUMERIC');
EDIT AGAIN: I came up with the fixed length stuff (I've been needing something like this for our environment, which is why I'm willing to do this coding). Let me know if this looks realistic to you or not.
SELECT SUM(CASE WHEN Data_Type IN ('INT','REAL','SMALLMONEY','SMALLDATETIME') THEN 4
WHEN Data_Type IN ('BIGINT','DATETIME','MONEY') THEN 8
WHEN Data_Type IN ('SMALLINT') THEN 2
WHEN Data_Type IN ('TINYINT','BIT') THEN 1
WHEN Data_Type IN ('CHAR','BINARY') THEN Character_Maximum_Length
WHEN Data_Type LIKE '%MAX%' OR Data_Type IN ('TEXT','IMAGE') THEN 2147483647
WHEN Data_Type IN ('NTEXT') THEN 2147483646
ELSE 0
END) AS ColFixedSize
FROM Information_Schema.Columns
WHERE Table_Name ='MyTable'
AND (Data_Type IN ('INT','REAL','SMALLMONEY','SMALLDATETIME','BIGINT','DATETIME','MONEY',
'SMALLINT','TINYINT','BIT','CHAR','BINARY','TEXT','IMAGE','NTEXT')
OR Data_Type LIKE '%MAX%');
Actually, I have SSMS, just no reports. That's why I am looking for DMVs etc to help me figure out how big this one record is. Looks like I need to write code to write the code to tell me sice I really don't want to write it by hand with 400 columns.
August 30, 2012 at 10:38 am
The code I gave you should give you sizes that you can add together without having to add each of the columns, Lynn. It grabs all the info into 4 nice little numbers.
August 30, 2012 at 10:46 am
Brandie Tarvin (8/30/2012)
The code I gave you should give you sizes that you can add together without having to add each of the columns, Lynn. It grabs all the info into 4 nice little numbers.
Using INFORMATION_SCHEMA.COLUMNS will give me the theoretical size of each record, not the actual size of the one record currently in the table.
August 30, 2012 at 10:48 am
Actually, I have SSMS, just no reports. That's why I am looking for DMVs etc to help me figure out how big this one record is. Looks like I need to write code to write the code to tell me sice I really don't want to write it by hand with 400 columns.
Fun times!
Rather than code, you could probably build this query in Excel too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2012 at 11:17 am
I think dm_db_partition_stats is what you want. Change the index_id depending on if the table is clustered or a heap.
SELECT so.name,
ps.used_page_count * 8 AS UsedKB,
ps.reserved_page_count * 8 AS ReservedKB
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.sysobjects so ON ps.object_id = so.id
WHERE PS.index_id = 1
ORDER BY ps.reserved_page_count DESC
August 30, 2012 at 11:25 am
Charles Hearn (8/30/2012)
I think dm_db_partition_stats is what you want. Change the index_id depending on if the table is clustered or a heap.
SELECT so.name,
ps.used_page_count * 8 AS UsedKB,
ps.reserved_page_count * 8 AS ReservedKB
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.sysobjects so ON ps.object_id = so.id
WHERE PS.index_id = 1
ORDER BY ps.reserved_page_count DESC
Modified slightly to use sys.objects instead of sys.sysobjects:
SELECT so.name,
ps.used_page_count * 8 AS UsedKB,
ps.reserved_page_count * 8 AS ReservedKB
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.objects so ON ps.object_id = so.object_id
WHERE PS.index_id = 1
ORDER BY ps.reserved_page_count DESC
Thanks, this query told me what I think I was trying to figure out. Much appreciated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply