如何在SQL中动态地从数据库中的各个表中获取列？ [英] How to get the columns from respective tables in a database dynamically in 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 

解决方案

不确定我理解你的问题。您是否正在寻找一种获取逗号分隔列表的方法或添加括号的方法？



这可能解决您的问题吗？

 DECLARE @TableName varchar（max）='Invoices'
 DECLARE @ColumnList varchar（max）
 SELECT @ColumnList = COALESCE（@ColumnList + '，'，''）+'['+ column_name +']'来自information_schema.columns WHERE table_name = @TableName 
 SELECT @ColumnList 

这是你的SQL查询应该是什么样子

 INSERT INTO [SOME_TABLE] 
（[Position] 
，[DisplayText] 
， [DisplayURL] 
，[ToolTip]）
 VALUES（SELECT Position，DisplayText，Display，URLToolTip 
 FROM [SOME_OTHER_TABLE] 
 WHERE [Position] = @ parent_id）

where子句的值应该是c＃代码中的参数。

 SqlParameter param = new SqlParameter 
 {
 ParameterName =@ parent_id，
 DbType = DbType.Int32，
 Value = parentID 
}; 

并添加到你的SqlCommand对象中

 cmd.Parameters.Add（PARAM）; 

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

解决方案

Not sure I understand your question. Are you looking for a way to get the comma separated list or a way to add the brackets?

Does this perhaps solve your problem?

DECLARE @TableName varchar(max) = 'Invoices'
DECLARE @ColumnList varchar(max)
SELECT @ColumnList = COALESCE(@ColumnList + ',', '') + '[' + column_name + ']' from information_schema.columns WHERE table_name = @TableName
SELECT @ColumnList

This is what your sql query should look like

INSERT INTO [SOME_TABLE]
           ([Position]
           ,[DisplayText]
           ,[DisplayURL]
           ,[ToolTip])
     VALUES (SELECT Position, DisplayText, Display, URLToolTip
			 FROM  [SOME_OTHER_TABLE]
			 WHERE [Position] = @parent_id)

The value for the where clause should be a parameter in your c# code.

SqlParameter param = new SqlParameter
            {
                ParameterName = "@parent_id",
                DbType = DbType.Int32,
                Value = parentID
            }; 

and added to your SqlCommand object like this

cmd.Parameters.Add(param);

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