Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Omit Data Length for Numeric Values Expand / Collapse
Author
Message
Posted Wednesday, August 21, 2013 1:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
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
Post #1486873
Posted Wednesday, August 21, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 13,331, Visits: 10,198
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1486899
Posted Wednesday, August 21, 2013 3:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1486974
Posted Thursday, August 22, 2013 2:47 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 1,972, Visits: 2,918
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1487527
Posted Thursday, August 22, 2013 3:41 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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
Post #1487550
Posted Thursday, August 22, 2013 4:48 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 1,972, Visits: 2,918
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1487584
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse