本文介绍了如何在SQL中动态地从数据库中的各个表中获取列?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我希望在SQL中动态地从数据库中的各个表中获取列。我能够获取没有方括号尴尬的列。但我需要这样做,因为有几个表的连字符列。



我尝试过:



核心代码如下:

选择@SourceColumnList = @SourceColumnList +','+ DATA_TYPE ='xml'时的情况'convert(varchar(max),'+ column_name +')'else column_name end 
from information_schema.columns
其中table_name = @TableName而DATA_TYPE不在('timestamp')

从information_schema.columns中选择@ DestinationColumnList = @ DestinationColumnList +','+ column_name

其中table_name = @TableName而DATA_TYPE不在('timestamp')


set @ SourceColumnList = RIGHT(@ SourceColumnList,Len(@SourceColumnList)-1)
set @ DestinationColumnList = RIGHT(@ DestinationColumnList,Len(@DestinationColumnList)-1)





选择

设置@SourceColumnList 





select

设置@DestinationColumnList 
解决方案



I want to get the columns from respective tables in a database dynamically in SQL. i am able to fetch columns without square braces embarrassed. But I need to do so since there is are columns with hyphen for few tables .

What I have tried:

Core Code is as below:

select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end 
from information_schema.columns 
where table_name = @TableName and DATA_TYPE not in ('timestamp') 

select @DestinationColumnList=  @DestinationColumnList+ ',' + column_name 
from information_schema.columns 
where table_name = @TableName and DATA_TYPE not in ('timestamp') 


set @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1) 
set @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)



select

set @SourceColumnList



select

set @DestinationColumnList
解决方案



这篇关于如何在SQL中动态地从数据库中的各个表中获取列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-29 11:48