Fetching MAX(LEN(ColumnName)) for all columns in table
-
To All:
Does anyone have a good SQL script that would fetch the MAX(LEN(...)) or MAX(DATALENGTH(...)) for all of the data resident in a given SQL table by column name? In other words, I'd like the results to be something like...
ColumnName MaxLength
================= ==================
OE_PO_No 50
Ord_Type 1
Ord_No 8
...
RMA_No 8
================= ==================
The reason I'm looking for this is that I used DTS to transfer a bunch of stuff from a non-SQL data source and ended up with a bunch of columns created as nVarChar(510). I'd like to drop and recreate the tables with column sizes that are more correctly sized for the actual data resident in the table. However, there are lots of columns and reviewing the data manually is taking a lot of time.
Thanks for your help.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Well, it may be crude, but I wrote the following and it works:
/******************************************************************************
This script will fetch the ACTUAL MAX DATA LENGTH of data resident
columns of the named table.
NOTE: You must change the value of the @TableName variable and be
pointed at the correct database when running this script.
*******************************************************************************/
DECLARE @TableName VarChar(50)
SET @TableName = 'OEOHdr'
CREATE Table #Columns
(
ColumnName VarChar(50)
, ActDataLength Int
 
INSERT INTO #Columns (ColumnName)
SELECT a.Name 'ColumnName'
FROM SysColumns a
, SysObjects b
WHERE b.Name LIKE @TableName
AND b.ID=a.ID
ORDER BY colid
DECLARE @ColumnName VarChar(50)
, @ActDataLength Int
DECLARE C1 CURSOR FOR
SELECT a.Name
FROM SysColumns a
, SysObjects b
WHERE b.Name LIKE @TableName
AND b.ID=a.ID
ORDER BY ColID
OPEN C1
FETCH C1 INTO @ColumnName
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Columns
SET ActDataLength = (SELECT MAX(LEN(@ColumnName)))
WHERE @ColumnName = ColumnName
FETCH C1 INTO @ColumnName
END
CLOSE C1
DEALLOCATE C1
SELECT *
FROM #Columns
DROP Table #Columns
GO
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Are you sure that tis is working.. this seems to give the size of the column name???
-
Yep, you're right! I just discovered that.
How do I fix it???
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Takes dynamic sql for this task. There's just no way around that. I usually do that stuff in vb >>
first loop
Select Name from dbo.SysObjects where XType = 'U' order by Name
for each table, list the columns
Select * from dbo.%tableName% where 1=0
then loop through all the fields returned and build the final statement ::
Select max(len(%ColumName%)) as %ColumnName%, ..... from %TableName%.
Then I usually go through all the fields returned again and insert the results in another table >> tableName, FieldName, StatsFetched
Takes a long time to code the first time but it's worth it after a while. Sorry I don't have an exemple on hand. Also once it's coded in vbs you can simply use that in a dts task.
-
Unfortunately, I don't write or have immediate access to writing in VB code, so I'm stuck doing this in T-SQL. I've changed my code (see below) to dynamic SQL, but now I end up with NULLs in all my ActDataLength fields.
What am I missing here?
*******************************************************************************
DECLARE @TableName VarChar(50)
SET @TableName = 'OEOHdr'
CREATE Table #Columns
(
ColumnName VarChar(50)
, ActDataLength Int
 
INSERT INTO #Columns (ColumnName)
SELECT a.Name 'ColumnName'
FROM SysColumns a
, SysObjects b
WHERE b.Name LIKE @TableName
AND b.ID=a.ID
ORDER BY colid
DECLARE @sqltext VarChar(8000)
DECLARE @ColumnName VarChar(50)
, @ActDataLength Int
SELECT @sqltext = '
DECLARE C1 CURSOR FOR
SELECT a.Name
FROM SysColumns a
, SysObjects b
WHERE b.Name LIKE ''' + @TableName + '''
AND b.ID=a.ID
OPEN C1
FETCH C1 INTO ''' + @ColumnName + '''
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #Columns
SET ActDataLength = (SELECT MAX(LEN(''' + @ColumnName + ''')))
FROM ''' + @TableName + '''
WHERE @ColumnName = ColumnName
FETCH C1 INTO ''' + @ColumnName + '''
END
CLOSE C1
DEALLOCATE C1
'
EXEC (@sqltext)
SELECT *
FROM #Columns
DROP Table #Columns
GO
*************************************************************************
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
DECLARE @TableName VarChar(50)
SET @TableName = 'OEOHdr'
CREATE Table #Columns
(
ColumnName VarChar(50)
, ActDataLength Int
INSERT INTO #Columns (ColumnName)
SELECT a.Name 'ColumnName'
FROM INFORMATION_SCHEMA.COLUMNS Where TABLE NAME = @TableName
DECLARE @sqltext VarChar(8000)
DECLARE @ColumnName VarChar(50)
, @ActDataLength Int
DECLARE C1 CURSOR FOR
SELECT ColumnName
FROM #Columns
OPEN C1
FETCH C1 INTO @ColumnName
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqltext = ' UPDATE #Columns SET ActDataLength = (SELECT MAX(LEN(' + @ColumnName + '))) FROM ' + @TableName
EXEC (@sqltext)
FETCH C1 INTO ''' + @ColumnName + '''
END
CLOSE C1
DEALLOCATE C1
SELECT *
FROM #Columns
DROP Table #Columns
GO
Just hope all your columns are varchar
* Noel -
This one only checks for the length of [n]chars and [n]varchars columns, prints the list of voided tables. This can be usefull if you only want to see the results and not do anything with them later on... but you'll get the idea.
Declare @Command as varchar(8000)
SET @Command = '
Declare @Cols varchar(8000)
Declare @Count as smallint
SET @Count = 0
set @Cols = ''''''?'''' AS TableName, ''
SELECT @Count = @Count + 1, @Cols = @Cols + ''MAX(LEN(['' + Name + ''])) AS ['' + Name + ''], '' from dbo.SysColumns where id = Object_id (''?'') AND XType IN (104,167,231,239)
SET @Cols = LEFT(@Cols, DATALENGTH(@Cols) - 2)
--PRINT @Cols
IF @Count > 0
BEGIN
EXEC (''Select '' + @Cols + '' FROM ?'')
END
ELSE
BEGIN
PRINT ''? doesn''''t have text columns to scan''
END
'
EXEC SP_MSForEachTable @Command
-
Wow! This is great! Thanks.
The prior post was close, but even after making some adjustments I ended up with NULLs in the ActDataLength column for ALL columns.
But THIS one really works! Thanks again.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
My bad, did test on an old db that I don't know very well.. I assumed some nulls were caused by a lack of usefull data.
Here's a better version :
Declare @Command as varchar(8000)
SET @Command = '
Declare @Cols varchar(8000)
Declare @Count as smallint
SET @Count = 0
set @Cols = ''''''?'''' AS TableName, ''
SELECT @Count = @Count + 1, @Cols = @Cols + ''MAX(ISNULL(LEN(['' + Name + '']), 0)) AS ['' + Name + ''], '' from dbo.SysColumns where id = Object_id (''?'') AND XType IN (104,167,231,239)
SET @Cols = LEFT(@Cols, DATALENGTH(@Cols) - 2)
--PRINT @Cols
IF @Count > 0
BEGIN
EXEC (''Select '' + @Cols + '' FROM ?'')
END
ELSE
BEGIN
PRINT ''? doesn''''t have text columns to scan''
END
'
EXEC SP_MSForEachTable @Command
-
Here's another way (credit goes to Mike H., friend of mine and a Microsoft Valued Professional in the SQL world), and this gives me the results in original format I was looking for:
DECLARE @TableName VarChar(50)
SET @TableName = 'OEOHdr' -- Change this as required
CREATE Table #Columns
(
ColumnName VarChar(50)
, ActDataLength Int
 
INSERT INTO #Columns (ColumnName)
SELECT a.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE TABLE_NAME = @TableName
DECLARE @sqltext VarChar(8000)
DECLARE @ColumnName VarChar(50)
, @ActDataLength Int
DECLARE C1 CURSOR FOR
SELECT ColumnName
FROM #Columns
OPEN C1
FETCH C1 INTO @ColumnName
WHILE @@fetch_status = 0
BEGIN
SET @sqltext = 'UPDATE #Columns SET ActDataLength = '
SET @sqlText = rtrim(@sqlText) + '(SELECT MAX(LEN(' + ltrim(rtrim(@ColumnName)) + ')) FROM ' + ltrim(rtrim(@TableName)) + ') WHERE ColumnName = ''' + ltrim(rtrim(@ColumnName)) + ''''
EXEC (@sqltext)
FETCH C1 INTO @ColumnName
END
CLOSE C1
DEALLOCATE C1
SELECT *
FROM #Columns
DROP Table #Columns
GO
Thanks to all!!!
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Works fine as long as you don't have 50 columns and 1M rows.
My version does a single pass per table, and all tables.
And as I said, it would be better to use a dts and use my original plan, which would give out the same results as the previous code presents
. -
Don't take offense. I am impressed with your solution, and I like the abililty to look at all the tables in a database. At the same time, that doesn't work too well if there are 4000 tables in the database and you only need the info regarding a handful of them.
Thanks for your effort!!!
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Now that's another problem
.I'll have to add this possibility when I remake my dts version of this script.
HTH.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply