SQL Iterate Over All Tables(SQL 遍历所有表)
问题描述
我正在运行以下代码以从具有特定列的所有表中提取所有相关行.外部 IF
应该检查该列是否存在于该迭代的表中.如果不是,它应该完成该迭代并移动到下一个表.如果表有 GCRecord
列,那么它应该检查该表是否会返回任何记录.如果没有要返回的记录,它应该结束该迭代并移动到下一个表.如果有记录,应该在SSMS中显示.
I am running the following code to extract all relevant rows from all tables that have a particular column. The outer IF
is supposed to check if the column exists on the table for that iteration. If not, it should finish that iteration and move to the next table. If the table has the GCRecord
column, it should then check to see if that table will return any records. If there are no records to return, it should end that iteration and move on to the next table. If there are records, it should display them in SSMS.
USE WS_Live
EXECUTE sp_MSforeachtable
'
USE WS_Live
IF EXISTS( SELECT *
FROM sys.columns
WHERE columns.Object_ID = Object_ID(''?'')
AND Name = ''GCRecord''
)
BEGIN
IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)
BEGIN
SELECT * FROM ? WHERE GCRecord IS NOT NULL
END
END
'
这似乎有效,因为 SSMS 只返回具有有效条目的网格.我不明白的是:为什么我仍然收到这些错误?
It seems to work because SSMS is only returning grids with valid entries. What I don't understand is: Why am I still getting these errors?
Msg 207, Level 16, State 1, Line 10
Invalid column name 'GCRecord'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'GCRecord'.
编辑
使用建议后,我有这个:
After using the suggestions, I have this:
USE WS_Live
EXECUTE sp_MSforeachtable
'
USE WS_Live
IF EXISTS(SELECT * FROM sys.columns WHERE columns.Object_ID = Object_ID(''?'')AND Name = ''GCRecord'')
BEGIN
IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)
BEGIN
EXEC('' SELECT * FROM ? WHERE GCRecord IS NOT NULL'')
END
END
'
返回此错误:
Msg 207, Level 16, State 1, Line 7
Invalid column name 'GCRecord'.
指的是这一行
IF EXISTS(SELECT * FROM sys.columns WHERE columns.Object_ID = Object_ID(''?'')AND Name = ''GCRecord'')
更新
我尝试嵌套 EXEC
语句但不起作用,但使用选定的答案我得到了我正在寻找的结果而没有错误.
I tried nesting EXEC
statements which did not work, but using the selected answer I got the results I was looking for without the errors.
推荐答案
在begin内部使用动态查询避免内部预编译代码,因为表不包含列'GCRecord
'
Use Dynamic query inside begin to avoid inner pre-compilation of code, for tables do not contain column 'GCRecord
'
USE WS_Live
GO
EXECUTE sp_MSforeachtable
'
IF EXISTS( SELECT *
FROM sys.columns
WHERE columns.Object_ID = Object_ID(''?'')
AND Name = ''GCRecord''
)
BEGIN
EXEC(''
IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)
BEGIN
SELECT * FROM ? WHERE GCRecord IS NOT NULL
END
'')
END
'
这篇关于SQL 遍历所有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:SQL 遍历所有表
基础教程推荐
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01