问题描述
SELECT SUBSTRING((从INFORMATION_SCHEMA.COLUMNS中选择(``select''，''+ COLUMN_NAME +''``+ DATA_TYPE +''''+''(''+ CHARACTER_MAXIMUM_LENGTH +'')''
在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)''
如果我执行此查询会出错...
SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+'' ''+''(''+CHARACTER_MAXIMUM_LENGTH+'')''from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)''
If i execute this query am getting error...
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '(' to data type int.
请告诉我该解决方案是什么
我尝试过的事情:
SELECT SUBSTRING((选择``，''+ COLUMN_NAME +''``+ DATA_TYPE +''''+ CONVERT(INT，''('')+ CHARACTER_MAXIMUM_LENGTH + CONVERT(INT，'')'')从INFORMATION_SCHEMA.COLUMNS
在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)
please tell me what is the solution for this
What I have tried:
SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+'' ''+CONVERT(INT,''('')+CHARACTER_MAXIMUM_LENGTH+CONVERT(INT,'')'') from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)
推荐答案
... + CONVERT(NVARCHAR(6), CHARACTER_MAXIMUM_LENGTH) + ...
[edit]
考虑一下，您可能还需要检查是否为空-只有VARCHAR和NVARCHAR列具有非空值
[edit]
Thinking about it, you probably want to check that for nulls as well - only VARCHAR and NVARCHAR columns have non-null values
... + CONVERT(NVARCHAR(6), ISNULL(CHARACTER_MAXIMUM_LENGTH, -1)) + ...
[/edit]
[/edit]
尝试以下查询，让我知道是否有任何问题
SELECT SUBSTRING((select``，''+ COLUMN_NAME +''``+ DATA_TYPE +''(''+ Convert(Varchar(20)，CHARACTER_MAXIMUM_LENGTH)+'')''''从INFORMATION_SCHEMA.COLUMNS
在哪里TABLE_NAME =''emp_payment''
FOR XML PATH(''''))，2,200000)
Try below query and let me know if any questions
SELECT SUBSTRING((select '',''+COLUMN_NAME+'' ''+DATA_TYPE+''(''+Convert(Varchar(20),CHARACTER_MAXIMUM_LENGTH)+'')'' from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=''emp_payment''
FOR XML PATH('''')),2,200000)
这篇关于如何在SQL中转换数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！