SQLServer 全库查询指定字段数据

今日收到一条需求，从旧的门户网站数据库中，导出所有的文章，以及文章发布账号和时间。由于数据库进行了分表，以及对系统程序的不熟悉，只能从全量搜索开始慢慢进行，步骤如下：

一、找出包含相关字段的表

通过随机抽取几个栏目的表结构进行观察，包含文章数据的表，都包含有这三个字段：title、user_name、add_time。所以第一步，就是找出包含这三个字段的表。

-- 查找包含title和user_name字段的表
SELECT t1.TABLE_NAME
FROM Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t1
JOIN Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t2
ON t1.TABLE_NAME = t2.TABLE_NAME
JOIN Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t3
ON t1.TABLE_NAME = t3.TABLE_NAME
WHERE t1.COLUMN_NAME = 'title'
AND t2.COLUMN_NAME = 'user_name'
AND t3.COLUMN_NAME = 'add_time'
AND t1.TABLE_CATALOG = 'Aicmsdbase'
AND t2.TABLE_CATALOG = 'Aicmsdbase'
AND t3.TABLE_CATALOG = 'Aicmsdbase';

AI解析：

这段SQL语句的目的是查询数据库Aicmsdbase中同时包含字段title、user_name和add_time的所有表。下面是对这段SQL语句的详细解析：

  1. 选择列：

    • SELECT t1.TABLE_NAME：选择查询结果中要显示的列，这里是表名。

  2. 从哪里选择：

    • FROM Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t1：从Aicmsdbase数据库的INFORMATION_SCHEMA.COLUMNS表中选择数据，并将这个表别名为t1。

  3. 连接条件：

    • JOIN Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t2 ON t1.TABLE_NAME = t2.TABLE_NAME：将t1表与Aicmsdbase.INFORMATION_SCHEMA.COLUMNS表再次进行连接，别名为t2，连接条件是t1和t2的TABLE_NAME相同，即它们属于同一个表。

    • JOIN Aicmsdbase.INFORMATION_SCHEMA.COLUMNS AS t3 ON t1.TABLE_NAME = t3.TABLE_NAME：再次将t1表与Aicmsdbase.INFORMATION_SCHEMA.COLUMNS表连接，别名为t3，连接条件同样是t1和t3的TABLE_NAME相同。

  4. 筛选条件：

    • WHERE t1.COLUMN_NAME = 'title'：筛选条件，只选择t1表中COLUMN_NAME为title的记录。

    • AND t2.COLUMN_NAME = 'user_name'：筛选条件，只选择t2表中COLUMN_NAME为user_name的记录。

    • AND t3.COLUMN_NAME = 'add_time'：筛选条件，只选择t3表中COLUMN_NAME为add_time的记录。

  5. 数据库目录：

    • AND t1.TABLE_CATALOG = 'Aicmsdbase'：确保t1表是在Aicmsdbase数据库目录下。

    • AND t2.TABLE_CATALOG = 'Aicmsdbase'：确保t2表也是在Aicmsdbase数据库目录下。

    • AND t3.TABLE_CATALOG = 'Aicmsdbase'：确保t3表也是在Aicmsdbase数据库目录下。

总结：这个查询通过三次自连接INFORMATION_SCHEMA.COLUMNS表，来找出数据库Aicmsdbase中同时包含title、user_name和add_time这三个字段的所有表的名称。每次连接都是基于表名相同，确保了这三个字段属于同一个表。

二、通过已知的表，查找相关数据

通过第一步查出来的表如下：

ai_channel_article_news
ai_channel_article_interactions
ai_channel_article_personnel
ai_channel_article_resources
ai_link
ai_feedback
ai_channel_article_plannings
ai_channel_article_works
ai_channel_article_infos
ai_channel_article_enforcements
ai_channel_article_financials
ai_channel_article_note
ai_leadermailbox
ai_statistics
ai_channel_article_disclosures
ai_channel_article_process
ai_channel_article_gservice
ai_channel_article_video
ai_channel_article_edus
ai_channel_article_supervisions
ai_channel_article_posts
ai_channel_article_wechat
ai_channel_article_cles
ai_channel_article_worksrule
ai_channel_article_thematic
ai_channel_article_down
ai_channel_article_cmgs
ai_channel_article_complaints
ai_channel_article_mvcs
ai_channel_article_content

通过执行动态语句，查找相关数据：

-- 切换到目标数据库
USE AIcmsdbase;

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

-- 创建一个表变量来存储表名
DECLARE @tableNames TABLE (TableName NVARCHAR(128));

-- 插入你的表名
INSERT INTO @tableNames (TableName)
VALUES
('ai_channel_article_news'),
('ai_channel_article_interactions'),
('ai_channel_article_personnel'),
('ai_channel_article_resources'),
('ai_channel_article_plannings'),
('ai_link'),
('ai_feedback'),
('ai_channel_article_works'),
('ai_channel_article_infos'),
('ai_channel_article_enforcements'),
('ai_channel_article_financials'),
('ai_channel_article_note'),
('ai_leadermailbox'),
('ai_statistics'),
('ai_channel_article_disclosures'),
('ai_channel_article_process'),
('ai_channel_article_gservice'),
('ai_channel_article_video'),
('ai_channel_article_edus'),
('ai_channel_article_supervisions'),
('ai_channel_article_posts'),
('ai_channel_article_wechat'),
('ai_channel_article_cles'),
('ai_channel_article_worksrule'),
('ai_channel_article_thematic'),
('ai_channel_article_down'),
('ai_channel_article_cmgs'),
('ai_channel_article_complaints'),
('ai_channel_article_mvcs'),
('ai_channel_article_content');

-- 构建动态SQL语句
SELECT @SQL = @SQL + 
N'SELECT ''' + TableName + ''' AS [Table], title, user_name, add_time FROM ' + QUOTENAME(TableName) + 
' UNION ALL ' 
FROM @tableNames;

-- 移除最后一个 UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

-- 输出构建的SQL语句（可选，用于调试）
PRINT @SQL;

-- 执行动态SQL
EXEC sp_executesql @SQL;

AI解析：

这段SQL脚本执行了以下操作：

  1. 切换数据库：

    • USE AIcmsdbase;：切换到名为AIcmsdbase的数据库。

  2. 声明变量：

    • DECLARE @SQL NVARCHAR(MAX);：声明一个名为@SQL的变量，用来存储构建的动态SQL语句，数据类型为NVARCHAR(MAX)，可以存储非常大的文本。

    • SET @SQL = N'';：初始化@SQL变量为空字符串。

  3. 创建表变量：

    • DECLARE @tableNames TABLE (TableName NVARCHAR(128));：声明一个名为@tableNames的表变量，用来存储表名，表变量有一个列TableName，数据类型为NVARCHAR(128)。

  4. 插入表名：

    • INSERT INTO @tableNames (TableName) VALUES (...);：向@tableNames表变量中插入一系列表名。

  5. 构建动态SQL语句：

    • SELECT @SQL = @SQL + ... FROM @tableNames;：通过遍历@tableNames表变量中的每个表名，构建一个动态SQL查询语句。每个表名对应的查询部分是SELECT '表名' AS [Table], title, user_name, add_time FROM QUOTENAME(表名)，并且使用UNION ALL将各个查询结果合并。

  6. 移除多余的UNION ALL：

    • SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);：由于构建的动态SQL语句最后会多出一个UNION ALL，所以需要将其移除。LEFT(@SQL, LEN(@SQL) - 10)函数将@SQL字符串的最后10个字符（即UNION ALL）截取掉。

  7. 输出构建的SQL语句（可选，用于调试）：

    • PRINT @SQL;：打印出构建的动态SQL语句，用于调试。

  8. 执行动态SQL：

    • EXEC sp_executesql @SQL;：执行存储在@SQL变量中的动态SQL语句。sp_executesql是一个用来执行动态SQL的存储过程。

总结：这段脚本首先切换到目标数据库，然后构建一个包含多个表的动态SQL查询语句，该查询语句将从每个表中选择title、user_name和add_time列，并将结果合并。最后，脚本执行这个动态构建的SQL语句。这个过程通常用于从多个表中提取相同的列，并将结果集合并为一个单一的结果集。

三、合并执行

当然，以上两个步骤可以合并成一个步骤执行：

-- 切换到目标数据库
USE AIcmsdbase;

-- 声明变量来存储动态SQL语句
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

-- 创建一个表变量来存储包含特定字段的表名
DECLARE @tableNames TABLE (TableName NVARCHAR(128));

-- 动态查找包含title、user_name和add_time字段的表
INSERT INTO @tableNames (TableName)
SELECT t1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS t1
JOIN INFORMATION_SCHEMA.COLUMNS AS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
JOIN INFORMATION_SCHEMA.COLUMNS AS t3 ON t1.TABLE_NAME = t3.TABLE_NAME
WHERE t1.COLUMN_NAME = 'title'
AND t2.COLUMN_NAME = 'user_name'
AND t3.COLUMN_NAME = 'add_time'
AND t1.TABLE_CATALOG = 'AIcmsdbase';

-- 构建动态SQL语句
SELECT @SQL = @SQL + 
N'SELECT ''' + TableName + ''' AS [Table], title, user_name, add_time FROM ' + QUOTENAME(TableName) + 
' UNION ALL ' 
FROM @tableNames;

-- 移除最后一个 UNION ALL
IF LEN(@SQL) > 0
BEGIN
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
    -- 输出构建的SQL语句（可选，用于调试）
    PRINT @SQL;
    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END

AI解析：

这段SQL脚本主要用于查询操作，目的是从AIcmsdbase数据库中找到包含title、user_name和add_time这三个字段的所有表，并构建一个动态SQL语句来从这些表中选择这些字段，然后将结果合并。下面是对脚本中每个部分的详细解析：

  1. 切换到目标数据库：

    USE AIcmsdbase;

    这一行将当前数据库上下文切换到AIcmsdbase。

  2. 声明变量：

    DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'';

    这里声明了一个名为@SQL的变量，用来存储构建的动态SQL语句，并初始化为空字符串。

  3. 创建表变量：

    DECLARE @tableNames TABLE (TableName NVARCHAR(128));

    声明一个名为@tableNames的表变量，用来存储包含特定字段的表名。

  4. 动态查找表：

    INSERT INTO @tableNames (TableName)
SELECT t1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS t1
JOIN INFORMATION_SCHEMA.COLUMNS AS t2 ON t1.TABLE_NAME = t2.TABLE_NAME
JOIN INFORMATION_SCHEMA.COLUMNS AS t3 ON t1.TABLE_NAME = t3.TABLE_NAME
WHERE t1.COLUMN_NAME = 'title'
AND t2.COLUMN_NAME = 'user_name'
AND t3.COLUMN_NAME = 'add_time'
AND t1.TABLE_CATALOG = 'AIcmsdbase';

    这一部分通过连接INFORMATION_SCHEMA.COLUMNS表，查找包含title、user_name和add_time字段的表，并将结果插入到@tableNames表变量中。

  5. 构建动态SQL语句：

    SELECT @SQL = @SQL + 
N'SELECT ''' + TableName + ''' AS [Table], title, user_name, add_time FROM ' + QUOTENAME(TableName) + 
' UNION ALL ' 
FROM @tableNames;

    遍历@tableNames表变量中的每个表名，构建一个动态SQL查询语句，每个表名对应的查询部分是SELECT '表名' AS [Table], title, user_name, add_time FROM QUOTENAME(表名)，并且使用UNION ALL将各个查询结果合并。

  6. 移除多余的UNION ALL：

    IF LEN(@SQL) > 0
BEGIN
    SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);
    -- 输出构建的SQL语句（可选，用于调试）
    PRINT @SQL;
    -- 执行动态SQL
    EXEC sp_executesql @SQL;
END

    使用LEFT函数将@SQL字符串的最后10个字符（即UNION ALL）截取掉。如果@SQL变量不为空，则输出构建的SQL语句（可选，用于调试），然后执行动态SQL语句。

总结：整个脚本主要用于查询操作，通过动态SQL查询多个表中的特定字段，并将结果合并。它没有直接修改任何表中的数据，只是查询并显示了这些数据。

5
  • 博客首页
  • 学习笔记
  • 杂七杂八
  • 碎碎碎念
  • 常用链接
  • HZZ 2009-2024
  • 备案号：桂ICP备20004319号-1
    • 消息盒子

    # 暂无消息 #

    只显示最新10条未读和已读信息

    创建
    • 日常
    • 工作
    • 旅行
    • 运动
    |
    确定
    位置已开启
    😃😊😉😒😘👌👍💖💋👏😂😜✌🎁☕🌹🤦👀😆🤢😈👻🙊😥😶😐😤😱🙏💪
    从媒体库选择
    插入图床图片
    控制台 登出