January 24, 2012 at 6:33 am
Hi,
I'm working with INFORMATION_SCHEMA.COLUMNS. I want to select the DATA_TYPE and CHARACTER_MAXIMUM_LENGTH fields, but where the result is 'nvarchar', '-1' I want to cast the -1 as 'MAX'... but only for this row.
I can do it if I only select a single row, but if i want to return all rows and show them all together, I either get 'MAX' for the nvachar -1 row and NULLS everywhere else, or an error 'conversion failed when converting the varchar value 'MAX' to data type int
i.e. run:
SELECT DATA_TYPE, (CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CHARACTER_MAXIMUM_LENGTH END) FROM INFORMATION_SCHEMA.COLUMNS
or
SELECT DATA_TYPE, (CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CHARACTER_MAXIMUM_LENGTH END) FROM INFORMATION_SCHEMA.COLUMNS
I really want something like:
nvarchar MAX
nvarchar MAX
varchar 100
varchar 22
char 1
etc, etc
Basically, I'm trying to create archive tables dynamically based on existing tables structures. I can't use SELECT INTO as the table structures change frequently and I need to move archive data between the tables. This is the last thing I'm stuck on..
Hope this makes sense.
Thanks, Andrew
January 24, 2012 at 6:38 am
SELECT DATA_TYPE,
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),CHARACTER_MAXIMUM_LENGTH) END
FROM INFORMATION_SCHEMA.COLUMNS
You'll still get NULLs because numeric data doesn't have a length.
January 24, 2012 at 6:43 am
Cadavre (1/24/2012)
SELECT DATA_TYPE,CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),CHARACTER_MAXIMUM_LENGTH) END
FROM INFORMATION_SCHEMA.COLUMNS
You'll still get NULLs because numeric data doesn't have a length.
Genius. Thanks very much - have been giving myself a headache looking at that
January 24, 2012 at 6:52 am
Had a quick look at it - your code will miss the precision/scale of decimal numeric data.
Try something like this: -
SELECT object_name(c.object_id),
t.NAME AS DATA_TYPE,
CASE WHEN t.NAME IN ('char','varchar','nchar','nvarchar')
THEN CASE WHEN c.max_length = - 1
THEN 'MAX'
ELSE CONVERT(VARCHAR(4), CASE WHEN t.NAME IN ('nchar','nvarchar') THEN c.max_length / 2
ELSE c.max_length END)
END
WHEN t.NAME IN ('decimal','numeric')
THEN CONVERT(VARCHAR(4), c.precision) + ',' + CONVERT(VARCHAR(4), c.scale)
ELSE ''
END + CASE WHEN xml_collection_id <> 0
THEN CASE WHEN is_xml_document = 1
THEN 'DOCUMENT '
ELSE 'CONTENT '
END + COALESCE((SELECT TOP 1 QUOTENAME(ss.NAME) + '.' + QUOTENAME(sc.NAME)
FROM sys.xml_schema_collections sc
INNER JOIN sys.schemas ss ON sc.schema_ID = ss.schema_ID
WHERE sc.xml_collection_ID = XML_collection_ID), 'NULL')
ELSE ''
END
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE OBJECT_SCHEMA_NAME(c.object_ID) <> 'sys'
January 24, 2012 at 7:51 am
thanks again. I've already got the precision and scale another way, but I'll have a look at putting this in instead. thanks, Andrew
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply