检查information_schema是否缺少给定表的列 [英] Check information_schema for absence of columns for given tables

问题描述

我在mysql DataBase中有表A，B和C.表A具有列D，E和F，表B具有G，H和I列，而表C具有列E，I和J.



现在我将表和列的单独字符串数组传递给存储过程：

  / *  从不注意程序的详细信息* /  
  BEGIN  
  DECLARE   @ arrOftables   VARCHAR （ 200 ）; 
  DECLARE   @ arrOftables   VARCHAR  （ 200 ）; 
 
  SET   @ arrOftables  =    A，B，C，; 
  SET   @ arrOfcolumns  =   D，E，F，G，H，I，J，; 
  END  

无需创建循环，严格使用信息模式。我需要一个sql语句，如果三个给定表中没有列，则将获取false，否则为true。表A，B和C必须存在，而列D，E，F，G，H，I，J的缺失将标记为false（0）true（1）。此刻，这就是我能做到的认为它失败了;

  SELECT  count（ 1 ）
  FROM  information_schema.tables 
  WHERE  table_name  IN （CONCAT（'  \''，REPLACE（@arrOftables，' \， ' ，' \ '  \，\''），' \ ' ' ））
  AND （ SELECT  count（ 1 ）
  FROM  information_schema.columns 
  WHERE  column_name  IN （REPLACE（ @ arrOfcolumns  ，'  \，'，'  \' \，\ ' '）））
  AND  table_schema =  @ DBName  
  INTO  cnt;  / *  之前已声明变量cnt * /  
 
  / *   
别介意我的CONCAT（）和REPLACE（）。只需使用它们
格式化一些愚蠢的字符
 * /  

请帮忙吗？

解决方案

尝试创建一个列/表名称表，然后使用

  SELECT  
 T. [name]  AS  Table_Name，
 AC。[name]  AS  Column_Name 
  FROM  sys.tables  AS  T 
  INNER   JOIN  sys.all_columns AC  ON  T. [object_id] = AC。 [object_id] 

带有交叉连接的

您的代码看起来像C＃和SQL的奇怪组合 - 将其粘贴到SSMS节目中语法错误。无论如何你需要创建动态sql（表示查询的字符串），如果你要尝试那些REPLACE的东西。



使用我在评论中建议的方法 - 以下内容已经过测试并正常工作

 IF OBJECT_ID（'tempdb.dbo。＃tables'）IS NOT NULL drop table #tables 
 IF OBJECT_ID（'tempdb.dbo。＃columns'）IS NOT NULL drop table #columns 
 
  - 将输入分解为临时表
 select * into #tables from dbo.fnSplitString（@arrOftables，'，'）
 select *从dbo.fnSplitString到#columns（@arrOfcolumns，'，'）
 
  - 整理输入中的任何空格（或在函数中执行此操作）
 update #tables set splitdata = LTRIM（RTRIM（splitdata））
 update #columns set splitdata = LTRIM（RTRIM（splitdata））
 
  - 我们已经'失败'
 DECLARE @retValue varchar（ 5）='错误'
 
  - 检查表是否全部存在
 DECLARE @expect int =（SELECT COUNT（*）FROM #tables）
 DECLARE @res i nt 
从#tables选择@res = COUNT（1）B 
 INNER JOIN INFORMATION_SCHEMA.tables ON A.TABLE_NAME = B.splitdata 
 
  - 如果所有表都存在移动on检查列
 IF @res = @expect 
 BEGIN 
 SET @expect =（SELECT COUNT（*）FROM #columns）
 
 SELECT @res = COUNT（*）FROM information_schema.columns B 
 INNER JOIN INFORMATION_SCHEMA.tables A A.TABLE_NAME = B.TABLE_NAME 
 INNER JOIN #tables T ON A.TABLE_NAME = T.splitdata 
 INNER JOIN #columns C ON B.COLUMN_NAME = C.splitdata 
 
 IF @res = @expect 
 BEGIN 
  - 列出的所有表都存在，列出的所有列都存在于这些表
 SET @retValue ='True'
 END 
 END 
 
 PRINT @retValue  -  RETURN值

我从 http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/20 13/09/19 / how-to-split-a-string-by-delimited-char-in-sql-server / [ ^ ]

I have Tables A, B, and C in mysql DataBase. Table A has columns D, E, and F, table B has G, H, and I columns, while table C has columns E, I, and J.

Now i passed separate string arrays of tables and columns to a stored procedure:

/*Never mind the procedur's detail*/
BEGIN
   DECLARE @arrOftables VARCHAR(200);
   DECLARE @arrOftables VARCHAR(200);

   SET @arrOftables = "A, B, C,"; 
   SET @arrOfcolumns = "D, E, F, G, H, I, J,"; 
END

Without creating a loop, strictly with information schema. I needed an sql statement that will fetch false if none of the columns exist in the three given tables, true otherwise. that is, tables A, B, and C must exist, while absence of columns D, E, F, G, H, I, J will flag false(0) true(1).at the moment, this is what i can think and its failing me;

SELECT count(1)
FROM information_schema.tables
WHERE table_name IN ( CONCAT('\'', REPLACE(@arrOftables, '\,', ' \'\, \''), '\'' ))
AND ( SELECT count(1)
      FROM information_schema.columns
      WHERE column_name IN (REPLACE(@arrOfcolumns, '\,', ' \'\, \'')))
AND table_schema = @DBName 
INTO cnt;   /*variable cnt had been declared before now*/

/*
   Never mind my CONCAT() and REPLACE(). just using them
   to format some stupid characters
*/

Any assistance please ?

解决方案

Try creating a table of column/table names then using

SELECT
    T.[name]        AS Table_Name,
    AC.[name]       AS Column_Name
FROM sys.tables AS T
INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id]

with a cross join

Your code looks like a strange mix of C# and SQL - pasting it into SSMS shows syntax errors. In any event you need to create dynamic sql ( a string representing the query) if you are going to try that REPLACE stuff.

Using the method I suggested in my comment - the following has been tested and works

IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL  drop table #tables
IF OBJECT_ID('tempdb.dbo.#columns') IS NOT NULL  drop table #columns

-- break down the input into temporary tables
select * into #tables from dbo.fnSplitString(@arrOftables, ',')
select * into #columns from dbo.fnSplitString(@arrOfcolumns, ',')

-- tidy up any spaces from the input (or do this in the function)
update #tables set splitdata = LTRIM(RTRIM(splitdata))
update #columns set splitdata = LTRIM(RTRIM(splitdata))

--Posit that we have 'failed'
DECLARE @retValue varchar(5) = 'False'

-- check the tables all exist
DECLARE @expect int = (SELECT COUNT(*) FROM #tables)
DECLARE @res int
select @res = COUNT(1) from #tables B 
INNER JOIN INFORMATION_SCHEMA.tables A ON A.TABLE_NAME = B.splitdata

-- if all tables exist move on to check the columns
IF @res = @expect
BEGIN
	SET @expect = (SELECT COUNT(*) FROM #columns)

	SELECT @res = COUNT(*) FROM information_schema.columns B 
	INNER JOIN INFORMATION_SCHEMA.tables A ON A.TABLE_NAME = B.TABLE_NAME 
	INNER JOIN #tables T ON A.TABLE_NAME = T.splitdata
	INNER JOIN #columns C ON B.COLUMN_NAME = C.splitdata

	IF @res = @expect
	BEGIN
		-- all tables listed exist and all columns listed exist on those tables
		SET @retValue = 'True'
	END
END

PRINT @retValue -- RETURN value

I got the split function from http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/19/how-to-split-a-string-by-delimited-char-in-sql-server/[^]

这篇关于检查information_schema是否缺少给定表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！