December 27, 2016 at 1:07 pm
Hello,
I have a scenario where I need to query the length of the string value given for each column in a table.
For example, I have a table called CUST with customer information.
If I run the following query:
SELECT LEN(customer_code) FROM CUST WHERE customer_code='ABC'
The query will return a value of 3 however, customer_code is just one of the columns in such table.
I could do the same for each nvarchar column in the table but I am wondering if there is an easier way to do it without manually typing the same query over and over for each column.
I also tried:
SELECT LEN((SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='CUST' AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
)) AS 'LENGTH' FROM CUST WHERE customer_code='ABC'
But received this error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I wish I could do something like:
SELECT LEN(*) FROM CUST WHERE customer_code='ABC'
December 27, 2016 at 1:12 pm
Try this.
DECLARE @TableName As varchar(500), @TableSchema As varchar(500)
DECLARE @sql NVARCHAR(MAX)
SET @TableName = 'Table_Name'
SET @TableSchema = 'dbo'
SELECT @sql = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' +
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
' AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
--AND DATA_TYPE like '%char%'
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @sql
EXECUTE (@SQL)
December 27, 2016 at 2:53 pm
wweraw25 (12/27/2016)
Try this.DECLARE @TableName As varchar(500), @TableSchema As varchar(500)
DECLARE @sql NVARCHAR(MAX)
SET @TableName = 'Table_Name'
SET @TableSchema = 'dbo'
SELECT @sql = STUFF((SELECT
'
UNION ALL
select ' + QUOTENAME(Table_Name,'''') + ' AS Table_Name, ' +
QUOTENAME(Column_Name,'''') + ' AS ColumnName, MAX(' +
CASE WHEN DATA_TYPE IN ('XML','HierarchyID','Geometry','Geography','text','ntext')
THEN 'DATALENGTH(' ELSE 'LEN(' END + QUOTENAME(Column_Name) +
')) as [Max Length], ' + QUOTENAME(C.DATA_TYPE,'''') + ' AS Data_Type, ' +
CAST(COALESCE(C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_SCALE,0) AS VARCHAR(10)) +
' AS Data_Width FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_Name)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = @TableName
AND table_schema = @TableSchema
--AND DATA_TYPE like '%char%'
ORDER BY COLUMN_NAME
FOR XML PATH(''),Type).value('.','varchar(max)'),1,11,'')
--print @sql
EXECUTE (@SQL)
You may want to do this in one pass instead of running it once for every column, particularly if you have a large table.
This will run the query once. If you want more information, you can use the above query to add the appropriate info.
DECLARE @TableName As varchar(500), @TableSchema As varchar(500)
DECLARE @sql NVARCHAR(MAX)
SET @TableName = 'TableName'
SET @TableSchema = 'TableSchema'
SET @sql = (
SELECT 'SELECT ', STUFF((
SELECT ', MAX(LEN(', QUOTENAME(c.COLUMN_NAME), ')) AS ', QUOTENAME(c.COLUMN_NAME + '_len')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.DATA_TYPE LIKE '%CHAR'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, ''), CHAR(10),
'FROM ', QUOTENAME(t.TABLE_SCHEMA), '.', QUOTENAME(t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_SCHEMA = @TableSchema
AND t.TABLE_NAME = @TableName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
PRINT @sql
EXEC sys.sp_executesql @sql
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 27, 2016 at 3:41 pm
The LEN() function returns the number of printable characters. It excludes trailing spaces, and for numeric and date columns it will count the number of characters in the default string representation of the value.
The DATALENGTH() function counts the number of bytes of storage, including double byte storage for Unicode data types.
For example:
PRINT LEN( GETDATE() );
19
PRINT DATALENGTH( GETDATE() );
8
DECLARE @FN NVARCHAR(20) = 'John';
PRINT LEN(@FN);
4
PRINT DATALENGTH( @FN );
8
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply