My interest was picked-up when I read your article about size optimization, and I very like your approach.
As a first time poster on this site, I am enthusiast as for sharing my case. This reply may be seen as a long one, if so go to "In short..."
In one contract few years ago, I had a desire to have a quick look of what table space usage could be optimized, but in a really more basic way than your approach.
I was curious to see how much wasted space was present with "over sized" string type fields for some big tables, but wanted to develop a simple query for that (yeah, one could say I was a little "naive" or "over optimistic" for my experience level at that time...).
Anyway, in the context that I was working from another one design, I wanted to have a quick look. I wished to design only one query if possible, even if it was not Christmas yet... (a dream cannot hurt, right ?)
As a discussion example for clarification here, one string field in a table could have been originally created with a 120 characters length in mind at design time, when in reality some long term usage shows the maximum length really "consumed" for all rows in this field is only 45 characters. Here, "long term" could be few years or few million rows, as you wish for your particular case.
This kind of information would then be for me a starting point to discuss or evaluate if it would be beneficial to reduce the length of this "longer-than-necessary" string field as a table size optimization point of view. In this example, one could decide to reduce the length field from 120 to 60 characters, for example.
In short, I was curious to find out the maximum field length usage for every string type fields at least in one table, and I began to search if someone else had created some tools or an article for that particular task. In the time I allowed myself, I find none. (yeah, one could say I could have invest more time, but I am probably a little "naive" after all...)
I was motivated to develop something basic but general purpose, small but giving efficient results. I did not want to have "Temp" tables or lots of steps, no really, maybe only one or a few very short queries was also in my design objectives (speaking "naive"...).
My first tries to develop something were not immediately successful, however with that pending desire in mind, I then fell on an article written by Grep Larsen showing me how I could have all the required information for one table, and my motivation was raised again.
If it does not hurt anyone here, I just want to say that this one particular inspiring article appeared in http://www.sqlserverexamples.com. Do not worry, I am still a very enthusiastic amateur of "SQL Server Central"... 🙂
Using this inspiration, I finally went with this approach at that time:
-----------------------------------------------------------------
-- Display the Maximum Usage Length for Each Column in a Table (a simple tool helping to optimize ANY existing table definition)
-- WRITTEN BY: BRUNO ARNOLD
-- DATE: 04/06/2008
--
-- Originally inspired by reading GREG LARSEN on this:
--
-- I mainly use this to quickly see (on big tables) if a character field length
-- is really using (or needing) that range of field length, thus helping to spot possible space optimization.
--
-- FOR EXAMPLE:
--In this example using the [SalesOrderDetail] table from [AdventureWorks], I "could securely" change
--the [CarrierTrackingNumber] field's design definition to nvarchar(12) instead of nvarchar(25),
--but ONLY if the future usage is not meant to be change, of course.
--In this case, the select shows 24 bytes for this field, but it is a nvarchar type,
--so it means you could use 12 characters in the "nvarchar" design definition.
--
-----------------------------------------------------------------
USE [AdventureWorks]
DECLARE @SomeSQLCode nvarchar(MAX)
,@AnalysedTable nvarchar(128)
,@AnalysedTableSchema nvarchar(128)
SET @AnalysedTable = 'SalesOrderDetail'
SET @AnalysedTableSchema = 'Sales' -- In some other cases, this will be set to 'dbo'
SET @SomeSQLCode = ''
SELECT@SomeSQLCode = @SomeSQLCode + ' ,MAX(ISNULL(datalength([' + COLUMN_NAME + ']),0)) AS ' + QUOTENAME(COLUMN_NAME) + CHAR(10)
FROMINFORMATION_SCHEMA.COLUMNS
WHERE[TABLE_NAME] = @AnalysedTable AND [TABLE_SCHEMA] = @AnalysedTableSchema
GROUP BY [ORDINAL_POSITION], [COLUMN_NAME]
ORDER BY [ORDINAL_POSITION], [COLUMN_NAME]
SELECT @SomeSQLCode = 'SELECT ' + NULLIF(SUBSTRING(@SomeSQLCode,3,8000),'') + ' FROM [' + @AnalysedTableSchema + '].[' + @AnalysedTable + ']'
PRINT @SomeSQLCode
EXEC sp_ExecuteSQL @SomeSQLCode
One could say that my "wish list" of objectives were not fully achieved, but at least I had fun doing that part.
After all, this code could serve as a basic start point to get all field size informations for all tables for all databases for all servers for all planets... (oups, dreaming again...)
Seriously, I did make sure to put the credits to Greg Larsen for the parts that inspired my own solution, because I think it is very important to do that every time we can, thus sending positive encouragements for good work.
Everyone likes positive messages from time to time.
And like what's going on SQL Server Central, "together" we can be better than alone.
Salutations,
Bruno Arnold