[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 TSQL .. how to get the distinct datalenght of all columns in the table?

Posted on 2012-08-15
26
Medium Priority
?
867 Views
Last Modified: 2012-08-20
Hello,

I have a CSV file that I dumped into a staging table in SQL, what I want to do now is figure out  each column's maximum datalenght.

Currently I use..

SELECT DISTINCT
[SHIPTO BDR NAME], DATALENGTH([SHIPTO BDR NAME]) AS StringLength
  FROM [OPPY_DWUSD].[dbo].[staged_dwusd]
  GROUP BY [SHIPTO BDR NAME]
  ORDER BY StringLength DESC

Open in new window


This will give me the maximum StringLenght of the the particular column, how can I expand this so that it is done on all the columns in the table or do I have to manually check each column with the above TSQL?

I have many columns and I would like to look at each one, select the distinct values and then calculate their StringLenght.

Any idea?

Thanks.
0
Comment
Question by:mirde
  • 11
  • 11
  • 4
26 Comments
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298138
Try something like this:

USE OPPY_DWUSD
GO

DECLARE @colName VARCHAR(100)
DECLARE @sql VARCHAR(2000)

SET @sql='SELECT '

DECLARE myCur CURSOR LOCAL
FOR
      SELECT c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.OBJECT_ID=c.OBJECT_ID
      WHERE t.name='staged_dwusd'

OPEN myCur

FETCH NEXT FROM myCur INTO @colName

WHILE @@FETCH_STATUS=0
BEGIN
      SET @sql=@sql + 'MAX(DATALENGTH('+@colName+')) AS '+@colName+', '
      FETCH NEXT FROM myCur INTO @colName
END

CLOSE myCur
DEALLOCATE myCur

SET @sql=SUBSTRING(@sql,1,LEN(@sql)-2)+' FROM staged_dwusd'

EXEC(@sql)
0
 

Author Comment

by:mirde
ID: 38298156
Hi LionKing,

Below is the error I got?

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@colName".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@colName".
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@colName".
Msg 137, Level 15, State 2, Line 20
Must declare the scalar variable "@colName".

Open in new window


Any ideas?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298182
The @colName variable is there, make sure it's pasted in your query.
This line:

DECLARE @colName VARCHAR(100)

Should be there.

P.S. You can remove the

USE OPPY_DWUSD
GO
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:mirde
ID: 38298244
tsql error ?
This is the error I get? am I doing something wrong..?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38298307
You can determine all the max lengths at once -- don't scan the table multiple times just for that.


SELECT
    MAX(DATALENGTH([SHIPTO BDR NAME])) AS [SHIPTO BDR NAME Max Length],
    MAX(DATALENGTH([NEXT COLUMN NAME])) AS [NEXT COLUMN NAME Max Length],
    MAX(DATALENGTH([NEXT COLUMN NAME2])) AS [NEXT COLUMN NAME2 Max Length],
    --...
FROM [OPPY_DWUSD].[dbo].[staged_dwusd]


If you need to, we can then use UNPIVOT to get the results into one row per column -- just let me know.
0
 

Author Comment

by:mirde
ID: 38298336
Hi Scott,

I would then have to replace "NEXT COLUMN NAME" with the column? This is kind of what I am doing now, looking at the column names, and changing my query to hit just that column.

I was more looking for a TSQL script that will look at the tables, take all the columns and output the DATALENGHT for each column in the table.

Is this possible?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38298496
>> I would then have to replace "NEXT COLUMN NAME" with the column? <<

Yes.


>> I was more looking for a TSQL script that will look at the tables, take all the columns and output the DATALENGHT for each column in the table. <<

So do you want the max based on the column *definition* or the actual *data* in the column?

That is, if the column is defined as, say, varchar(30), but the longest data is only 25, do you want to see 30 or 25 as the result for your query?


>> Is this possible? <<

Yes, it's possible for either of those, just need to know what specifically you want to do :-) .
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38298517
Add a space after the select in the

SET @sql='SELECT '
0
 

Author Comment

by:mirde
ID: 38300624
Scott,

What I want to see is, if the column is defined as varchar(100) and the longest data entry is say. 70 characters then I want it to report back 70, that way I can optimize varchar(100) to something smaller.

Thanks for your help.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300636
If you want that you should change the query to this:

USE OPPY_DWUSD
GO

DECLARE @colName VARCHAR(100)
DECLARE @sql VARCHAR(2000)

SET @sql='SELECT '

DECLARE myCur CURSOR LOCAL
FOR
      SELECT c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.OBJECT_ID=c.OBJECT_ID
      WHERE t.name='staged_dwusd'

OPEN myCur

FETCH NEXT FROM myCur INTO @colName

WHILE @@FETCH_STATUS=0
BEGIN
      SET @sql=@sql + 'MAX(LEN('+@colName+')) AS '+@colName+', '
      FETCH NEXT FROM myCur INTO @colName
END

CLOSE myCur
DEALLOCATE myCur

SET @sql=SUBSTRING(@sql,1,LEN(@sql)-2)+' FROM staged_dwusd'

EXEC(@sql)

This should work if you copy+paste...
Let us know.
0
 

Author Comment

by:mirde
ID: 38300655
I copied the TSQL that you posted LionKing, it runs and then throws these errors:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'COMMIT'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DATE'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DATE'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MONTH'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MONTH'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YEAR'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'YEAR'.

Open in new window


The DB "OPPY_DWUSD" is there and so is the "staged_dwusd" table which has a dump of the CSV file using SSIS.

Looking at my column names...

 tsql error
It could be because I have spaces in the name? Anyway around that?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300747
Yes, you are correct. It's the spaces...
This could do the trick.

USE OPPY_DWUSD
GO

DECLARE @colName VARCHAR(100)
DECLARE @sql VARCHAR(2000)

SET @sql='SELECT '

DECLARE myCur CURSOR LOCAL
FOR
      SELECT c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.OBJECT_ID=c.OBJECT_ID
      WHERE t.name='staged_dwusd'

OPEN myCur

FETCH NEXT FROM myCur INTO @colName

WHILE @@FETCH_STATUS=0
BEGIN
      SET @sql=@sql + 'MAX(LEN(['+@colName+'])) AS ['+@colName+'], '
      FETCH NEXT FROM myCur INTO @colName
END

CLOSE myCur
DEALLOCATE myCur

SET @sql=SUBSTRING(@sql,1,LEN(@sql)-2)+' FROM staged_dwusd'

EXEC(@sql)
0
 

Author Comment

by:mirde
ID: 38300885
Getting close I think.. running the latest revision of your tsql gets me:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'LEN'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'FR ST F'.

Open in new window

0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300904
Do this, change the EXEC(@sql) to PRINT(@sql)
Run the query
Copy and paste whatever is printed in the results (Messages tab)

Now you can see where the error is... If you need help, post the result of the "print" and I'll help you find the error.

Thanks,
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300908
P.S. Do any of your column names have simple quotations (')?
0
 

Author Comment

by:mirde
ID: 38300925
No they do not, I think it could be because the dump table has 200+ columns?

The query printed has 2002 characters, then it looks like it cuts off; which then just errors out.

Is there a way to increase that or do in batch? 2002 characters, it stops at..

MAX(LEN([FR CITY])) AS [FR CITY], MAX(LEN([FR S F

Open in new window


There are more columns after that, I think it might be a character limitation? Anyway to increase that for the tsql query?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38300944
Yes, I changed it to 8000.

USE OPPY_DWUSD
GO

DECLARE @colName VARCHAR(1000)
DECLARE @sql VARCHAR(8000)

SET @sql='SELECT '

DECLARE myCur CURSOR LOCAL
FOR
      SELECT c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.OBJECT_ID=c.OBJECT_ID
      WHERE t.name='staged_dwusd'

OPEN myCur

FETCH NEXT FROM myCur INTO @colName

WHILE @@FETCH_STATUS=0
BEGIN
      SET @sql=@sql + 'MAX(LEN(['+@colName+'])) AS ['+@colName+'], '
      FETCH NEXT FROM myCur INTO @colName
END

CLOSE myCur
DEALLOCATE myCur

SET @sql=SUBSTRING(@sql,1,LEN(@sql)-2)+' FROM staged_dwusd'

EXEC(@sql)
0
 

Author Comment

by:mirde
ID: 38300985
So close, I think 8000 is about 80% way through our columns.

I tried to change that higher and I get..

Msg 131, Level 15, State 3, Line 3
The size (10000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

Open in new window


Do you know if we can change that for this tsql query?
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301020
8000 is the max for a varchar... Can you try changing that line to this

DECLARE @sql VARCHAR(MAX)


If it doesn't work, I think you'd have to do it in two batches, let me know to see if I can tweak the query for you...
0
 

Author Comment

by:mirde
ID: 38301151
I changed that, now I got:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'ArchiveFileName FROM staged_dwusd'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ArchiveFileName FROM staged_dwusd'.

Open in new window


This is the last column in my table.

Changing EXEC(@SQL) to PRINT, It does not catch everything.

Is it possible to limit the columns in the SQL query at all? I can then run this twice to get the whole picture.
0
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 500 total points
ID: 38301179
You could process it by column name, let's say you first query the columns with names start from A-L and then query from M-Z.

Something like:

USE OPPY_DWUSD
GO

DECLARE @colName VARCHAR(1000)
DECLARE @sql VARCHAR(8000)

SET @sql='SELECT '

DECLARE myCur CURSOR LOCAL
FOR
      SELECT c.name
      FROM sys.tables t INNER JOIN sys.columns c ON
      t.OBJECT_ID=c.OBJECT_ID
      WHERE t.name='staged_dwusd' AND c.name LIKE '[A-L]%'

OPEN myCur

FETCH NEXT FROM myCur INTO @colName

WHILE @@FETCH_STATUS=0
BEGIN
      SET @sql=@sql + 'MAX(LEN(['+@colName+'])) AS ['+@colName+'], '
      FETCH NEXT FROM myCur INTO @colName
END

CLOSE myCur
DEALLOCATE myCur

SET @sql=SUBSTRING(@sql,1,LEN(@sql)-2)+' FROM staged_dwusd'

EXEC(@sql)

And on your second query you can change [A-L] to [M-Z]...
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38301190
Since you want the actual lengths, you need to do this:


DECLARE @sql varchar(max)

SELECT @sql = (
    SELECT
        'MAX(LEN([' + CAST(c.name AS varchar(128)) + '])) AS ' +
        '[' + CAST(c.name AS varchar(128)) + '_Max_Length' + '],'
    FROM [OPPY_DWUSD].sys.columns c
    INNER JOIN [OPPY_DWUSD].sys.types t ON
        t.system_type_id = c.system_type_id AND
        t.user_type_id = c.user_type_id
    WHERE
        c.object_id = OBJECT_ID(N'[OPPY_DWUSD].[dbo].[staged_dwusd]') AND
        (t.name LIKE N'%char%' OR t.name LIKE N'%bin%')
    ORDER BY
        c.column_id
    FOR XML PATH('')
)

SET @sql = 'SELECT ' + LEFT(@sql, LEN(@sql) - 1) + ' FROM [OPPY_DWUSD].[dbo].[staged_dwusd]'

PRINT @sql
EXEC(@sql)
0
 

Author Comment

by:mirde
ID: 38301273
LionKing, it dies at:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'ArchiveFileName FROM staged_dwusd'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ArchiveFileName FROM staged_dwusd'.

Open in new window


I then tried the tsql code by Scott and it worked.. Interestingly enough, the column LionKing's errors out at reports back as:

ArchiveFileName and length is "NULL"..
0
 

Author Closing Comment

by:mirde
ID: 38301286
Exactly what I needed, thanks for the solution Scott and LionKing for the continuous effort to solve my problem.
0
 
LVL 13

Expert Comment

by:LIONKING
ID: 38301296
Yeah... My solution is very dirty compared to Scott's.
There you go...

Thanks Scott!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38301406
You're welcome! (both of you!)


FYI, be aware that PRINT has a limited buffer size and thus sometimes won't list the entire contents of a varchar(max) value, but that does NOT necessarily mean that the variable itself doesn't contain more characters.

SELECT is somewhat longer but it is limited as well.

Also, the settings in SSMS can limit the results of output for one column.

When using PRINT for (potentially) very long variables, you might want to do something like this:

PRINT SUBSTRING(@varchar_max_var, 00001, 16000)
PRINT SUBSTRING(@varchar_max_var, 16001, 16000)
PRINT SUBSTRING(@varchar_max_var, 32001, 16000)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question