I've searched for this up and down, but I can't find how to query for a set of rows.
When I query the DB for the rows themselves, that's simple enough.
SELECT *
FROM dbo.[tablename]
WHERE CreatedDate < '2012-12-31-00:00:00'
But I'm not sure how to apply something like sp_spaceused to this.
We need to sum data size of each of the column like below
select SUM(datalength(col1))+SUM(datalength(col2))+.. from tableName
WHERE CreatedDate < '2012-12-31-00:00:00'
Here is a dynamic query that will fetch columns for the table and then add up the size for each column in a row and sum up the total size.
declare @table nvarchar(20)
declare @whereClause nvarchar(50)
declare @sql nvarchar(max)
--initialize those two values
set @table = 'tableName'
set @whereClause = ' CreatedDate < ''2012-12-31-00:00:00'' '
set @sql = 'select ' + ' sum((0'
select @sql = @sql + ' + isnull(datalength(' + name + '), 1)'
from sys.columns where object_id = object_id(@table)
set @sql = @sql + ')) as totalSize from ' + @table + @whereClause
select @sql
exec (@sql)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments