August 21, 2013 at 1:01 pm
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:
ColumnNameColumnDataType
backup_set_idint(4)
backup_set_uuiduniqueidentifier(16)
media_set_idint(4)
first_family_numbertinyint(1)
first_media_numbersmallint(2)
last_family_numbertinyint(1)
last_media_numbersmallint(2)
catalog_family_numbertinyint(1)
catalog_media_numbersmallint(2)
positionint(4)
expiration_datedatetime(8)
software_vendor_idint(4)
namenvarchar(256)
descriptionnvarchar(510)
user_namenvarchar(256)
software_major_versiontinyint(1)
software_minor_versiontinyint(1)
software_build_versionsmallint(2)
time_zonesmallint(2)
mtf_minor_versiontinyint(1)
first_lsnnumeric(13)
last_lsnnumeric(13)
checkpoint_lsnnumeric(13)
database_backup_lsnnumeric(13)
database_creation_datedatetime(8)
backup_start_datedatetime(8)
backup_finish_datedatetime(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:
ColumnNameColumnDataType
backup_set_idint
backup_set_uuiduniqueidentifier
media_set_idint
database_namenvarchar(256)
server_namenvarchar(256)
machine_namenvarchar(256)
last_media_numbersmallint
catalog_family_numbertinyint
catalog_media_numbersmallint
positionint
expiration_datedatetime
software_vendor_idint
namenvarchar(256)
descriptionnvarchar(510)
user_namenvarchar(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
August 21, 2013 at 1:36 pm
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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 21, 2013 at 3:21 pm
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
Consultant - Straight Path Solutions
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
August 22, 2013 at 2:47 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 22, 2013 at 3:41 pm
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
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 22, 2013 at 4:48 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply