SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Omit Data Length for Numeric Values


Omit Data Length for Numeric Values

Author
Message
AndrewSQLDBA
AndrewSQLDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4428 Visits: 3427
Hello Everyone
I am merely playing around with an idea today. It is slow today, so I needed to teach myself something new. But as with some things, I have ran into a snag. I could do this manually, but that never any fun.

I am executing a select query that returns the column names and the data type for a single table. I am using a system table in the MSDB for sample purpose, since we all have an MSDB database to use.


SELECT
c.name AS ColumnName
, t.name+'('+CAST(c.max_length AS varchar(3))+')' AS ColumnDataType
FROM
sys.all_columns c
JOIN
sys.types t
ON
c.user_type_id
= t.user_type_id
WHERE object_id =
( SELECT object_id
FROM sys.tables
WHERE name = 'backupset')
ORDER BY c.column_id ASC



That will return this resultset:

ColumnName ColumnDataType
backup_set_id int(4)
backup_set_uuid uniqueidentifier(16)
media_set_id int(4)
first_family_number tinyint(1)
first_media_number smallint(2)
last_family_number tinyint(1)
last_media_number smallint(2)
catalog_family_number tinyint(1)
catalog_media_number smallint(2)
position int(4)
expiration_date datetime(8)
software_vendor_id int(4)
name nvarchar(256)
description nvarchar(510)
user_name nvarchar(256)
software_major_version tinyint(1)
software_minor_version tinyint(1)
software_build_version smallint(2)
time_zone smallint(2)
mtf_minor_version tinyint(1)
first_lsn numeric(13)
last_lsn numeric(13)
checkpoint_lsn numeric(13)
database_backup_lsn numeric(13)
database_creation_date datetime(8)
backup_start_date datetime(8)
backup_finish_date datetime(8)


I have shortened the resultset by a few rows. I would like to be able to not show the max_length of the data type for all non-character data types.

So for example:

ColumnName ColumnDataType
backup_set_id int
backup_set_uuid uniqueidentifier
media_set_id int
database_name nvarchar(256)
server_name nvarchar(256)
machine_name nvarchar(256)
last_media_number smallint
catalog_family_number tinyint
catalog_media_number smallint
position int
expiration_date datetime
software_vendor_id int
name nvarchar(256)
description nvarchar(510)
user_name nvarchar(256)

I have tried a CASE statement in the JOIN clause, that only limits the rows returned.

Does anyone have an idea as to how to correctly make this work?

Thank you in advance for all your assistance, suggestions and comments

Andrew SQLDBA
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62155 Visits: 13297
This comes close:


SELECT
c.name AS ColumnName
, t.name + CASE WHEN (c.precision <> 0 OR c.scale <> 0)
THEN ''
ELSE '('+CAST(c.max_length AS varchar(3))+')'
END AS ColumnDataType
,c.precision
,c.scale
FROM
sys.all_columns c
JOIN
sys.types t
ON
c.user_type_id
= t.user_type_id
WHERE object_id =
( SELECT object_id
FROM sys.tables
WHERE name = 'backupset')
ORDER BY c.column_id ASC;



It does show the maximum length for a uniqueidentifier, which technically holds characters :-)


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jack Corbett
  Jack Corbett
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44395 Visits: 14925
I think I'd do this:

SELECT
c.name AS ColumnName,
t.name + CASE WHEN T.NAME LIKE '%char%'
THEN CASE WHEN C.max_length > 0 THEN '(' + CAST(c.max_length AS VARCHAR(3)) + ')'
ELSE '(max)'
END
WHEN T.NAME LIKE '%binary' AND
C.max_length > 0 THEN '(' + CAST(c.max_length AS VARCHAR(3)) + ')'
ELSE ''
END AS ColumnDataType,
c.precision,
c.scale
FROM
sys.all_columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19537 Visits: 7410
You have to go further than that, including considerations of datetime, datetime2, etc..

NOTE: The code below does NOT consider any user-defined data types.



-- list column data type, including len(s), if applicable
CASE WHEN c.is_computed = 1 THEN 'AS ' +
(SELECT definition FROM sys.computed_columns cc WHERE cc.object_id = c.object_id AND cc.column_id = c.column_id) +
CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
ELSE t.name + CASE
WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN
'(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(10)) + ')' END
WHEN t.name IN ('datetime2', 'time') THEN
'(' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('decimal', 'numeric') THEN
'(' + CAST(c.precision AS varchar(3)) + ', ' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('float', 'real') THEN
'(' + CAST(c.precision AS varchar(3)) + ')'
ELSE '' END +
CASE WHEN c.is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'
END




SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Erland Sommarskog
Erland Sommarskog
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 Visits: 875
Here is a query that I have canned. It's part of a tool, and the purpose is to return information about all user-defined types. (But I realised today that it fails to return information about CLR types and table type).

The gist is fairly close to Scott's query, although he had forgotten datetimeoffset. And he had to failed to adjust the length for nchar/nvarchar. A complete query should also cover xml columns with a schema collection. I see now that my query does not handle MAX columns.

   SELECT typename = CASE N.N WHEN 1 THEN lower(a.name)
WHEN 2 THEN lower(s.name) + '.' + lower(a.name)
END,
typedef = b.name +
CASE WHEN b.name IN ('nchar', 'nvarchar')
THEN '(' + ltrim(str(a.max_length / 2)) + ')'
WHEN b.name IN ('char', 'varchar', 'binary', 'varbinary')
THEN '(' + ltrim(str(a.max_length)) + ')'
WHEN b.name IN ('decimal', 'numeric')
THEN '(' + ltrim(str(a.precision)) + ',' +
ltrim(str(a.scale)) + ')'
WHEN b.name IN ('datetime2', 'time', 'datetimeoffset')
THEN '(' + ltrim(str(a.scale)) + ')'
ELSE ''
END
FROM sys.types a
JOIN sys.types b ON a.system_type_id = b.system_type_id
JOIN sys.schemas s ON a.schema_id = s.schema_id
CROSS JOIN (SELECT N = 1 UNION ALL SELECT 2) AS N
WHERE a.user_type_id <> a.system_type_id
AND b.user_type_id = b.system_type_id
ORDER BY a.name



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19537 Visits: 7410
Good points about the Unicode and datetimeoffset (I've never personally used that data type):




-- list column data type, including len(s), if applicable
CASE WHEN c.is_computed = 1 THEN 'AS ' +
(SELECT definition FROM sys.computed_columns cc WHERE cc.object_id = c.object_id AND cc.column_id = c.column_id) +
CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END
ELSE t.name + CASE
WHEN t.name LIKE '%n%char%' THEN
'(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS varchar(10)) + ')' END

WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN
'(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(10)) + ')' END
WHEN t.name IN ('datetime2', 'time', 'timeoffset') THEN
'(' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('decimal', 'numeric') THEN
'(' + CAST(c.precision AS varchar(3)) + ', ' + CAST(c.scale AS varchar(3)) + ')'
WHEN t.name IN ('float', 'real') THEN
'(' + CAST(c.precision AS varchar(3)) + ')'
ELSE '' END +
CASE WHEN c.is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'
END




SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search