校验全库缺失某列索引的全部表列表-SQL Server版本

本文提供了一个SQL存储过程,用于检查指定数据库前缀下的所有表中特定字段是否已建立索引。通过创建临时表和使用游标遍历,此过程能够有效识别丢失索引的表。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在大型系统的表结构设计中,可能所有层级子表都会冗余根级主表的记录,这样在后续使用某一条根表相关数据时,只需要一个条件过滤即可。但是如果新人或因为某些不可控原因,导致这个冗余列索引缺失,这会对后续数据使用带来很大影响。如何知道哪些有这个字段的表没有创建索引呢?下面给出了解决办法!

一、创建函数

USE tempdb
GO

IF OBJECT_ID ('dbo.PR_QUERY_COL_INDEX_LOST') IS NOT NULL
        DROP PROCEDURE dbo.PR_QUERY_COL_INDEX_LOST
GO

CREATE PROCEDURE PR_QUERY_COL_INDEX_LOST(
     @dbname_prefix VARCHAR(300), --数据库前缀名
     @col_name      VARCHAR(300)  --字段名
     )
AS 
BEGIN
  DECLARE 
    @tmpSql          VARCHAR(900),
    @dbname          VARCHAR(300),
    @tablename       VARCHAR(300),
    @tableid         INT,
    @indid           INT,
    @lostIndex       INT,
    @i               INT

  CREATE TABLE #tmp_lost_col_index(
    dbname VARCHAR(300),
    tablename VARCHAR(300)
  )
  
  CREATE TABLE #tmp_tbs(
    tablename VARCHAR(300),
    tableid INT
  )
  
  CREATE TABLE #tmp_idx(
    indid INT
  )
           
  --1.定义游标(所有符合前缀条件的库名)
  DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases WHERE name LIKE @dbname_prefix + '%'
  --2.打开游标
  OPEN dbs 
  --3.获取记录
  FETCH dbs INTO @dbname  
  --4.循环处理每个库
  WHILE @@FETCH_STATUS = 0
  BEGIN
    --4.1 清空临时表
    TRUNCATE TABLE #tmp_tbs
    
    --4.2处理临时表
    SELECT @tmpSql = 'INSERT INTO #tmp_tbs SELECT name, id FROM ' + @dbname + '..sysobjects WHERE type = ''U'' AND EXISTS (SELECT 1 FROM ' + @dbname + '..syscolumns WHERE name = ''' + @col_name + ''' AND id = ' + @dbname + '..sysobjects.id)'
    PRINT @tmpSql
    EXECUTE (@tmpSql)
    
    --4.2.1 定义表列表游标 
    DECLARE tbs CURSOR FOR SELECT tablename, tableid FROM #tmp_tbs
    --4.2.2 打开表列表游标
    OPEN tbs
    --4.2.3 获取记录
    FETCH tbs INTO @tablename, @tableid
    --4.2.4状态为正常
    WHILE @@FETCH_STATUS = 0
    BEGIN
      --4.2.5处理所有索引
      TRUNCATE TABLE #tmp_idx
      SELECT @tmpSql = 'INSERT INTO #tmp_idx SELECT indid FROM ' + @dbname + '..sysindexes WHERE id = ' + CONVERT(VARCHAR, @tableid) + ' AND indid > 0 AND indid < 255'
      EXECUTE (@tmpSql)
      
      SELECT @lostIndex = -1
      --4.2.5.1 循环处理每个索引
      DECLARE idxs CURSOR FOR SELECT indid FROM #tmp_idx
      OPEN idxs
      FETCH idxs INTO @indid
      WHILE @@FETCH_STATUS = 0 
      BEGIN
            SELECT @i = 1
            WHILE @i <= 31
            BEGIN             
              --如果字段相同,则修改状态并跳出循环 
              IF index_col(@dbname + '..' + @tablename, @indid, @i) = @col_name
              BEGIN
                SELECT @lostIndex = 1
                BREAK
              END
              SELECT @i = @i + 1
            END
            --标记大于1时代表对应字段在索引中最低出现了一次,判断下一个表
            IF @lostIndex > -1 
            BEGIN
              BREAK
            END
            FETCH idxs INTO @indid
      END
      CLOSE idxs
      DEALLOCATE idxs
      
      IF @lostIndex < 0
      BEGIN
        INSERT INTO #tmp_lost_col_index SELECT @dbname, @tablename
      END
      
      --4.2.6处理下一条
      FETCH tbs INTO @tablename, @tableid
    END
    CLOSE tbs
    DEALLOCATE tbs
    
    --4.3 处理下一条
        FETCH dbs INTO @dbname
  END
  --5.关闭游标
  CLOSE dbs
  --6.销毁游标
  DEALLOCATE dbs
   
  
  SELECT dbname, tablename FROM #tmp_lost_col_index
   
  DROP TABLE #tmp_idx  
  DROP TABLE #tmp_tbs
  DROP TABLE #tmp_lost_col_index
END

GO

二、执行函数

USE tempdb
GO

EXECUTE tempdb..PR_QUERY_COL_INDEX_LOST 'DB_TASK', 'N_ID'
GO
三、查看结果
执行完毕后返回的结果集就是我们想要的结果。在需要时也可以修改存储过程中的临时表为实体表,根据需求调整即可
### SQL Server 相关的就业岗位和职责 #### 1. 数据库管理员 (DBA) 数据库管理员负责管理和维护SQL Server实例及其所承载的数据。主要职责包括但不限于: - 安装配置新的SQL Server环境并确保其稳定运行。 - 执行备份恢复操作,制定灾难恢复计划以保障数据安全[^1]。 - 对性能进行监控优化,通过索引调整、查询重写等方式提高响应速度。 ```sql BACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase.bak'; RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backup\MyDatabase.bak' WITH REPLACE; ``` #### 2. 数据库开发者 专注于利用T-SQL编写高效可靠的存储过程、触发器以及视图等功能模块来满足业务需求的应用程序接口设计者称为数据库开发者。具体任务如下: - 设计实现符合第三范式的表结构模型。 - 编写复杂的联接语句完成跨检索分析作业。 - 开发ETL流程用于不同源之间的数据迁移转换处理。 ```sql CREATE PROCEDURE GetCustomerOrders @CustomerId INT AS BEGIN SELECT c.Name, o.OrderDate, od.ProductName, od.Quantity FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE c.CustomerID = @CustomerId; END; ``` #### 3. BI分析师 / 报告设计师 BI分析师基于企业内部积累下来的历史交易记录构建可视化报工具帮助决策层更好地理解现状趋势预测未来走向;而报告设计师则侧重于按照既定模板样式制作定期发布的各类统计图文档资料。两者共同承担的工作内容涉及: - 使用Power BI Desktop连接到SQL Server Analysis Services创建交互式仪板展示关键绩效指标(KPIs)[^2]。 - 应用MDX或DAX达式挖掘多维立方体中的潜在价值信息支持管理层做更明智的选择判断。 ```dax EVALUATE SUMMARIZE( Sales, "TotalSales", SUM(Sales[Amount]) ) ORDER BY [TotalSales] DESC ``` #### 4. ETL工程师 ETL(Extract Transform Load)工程师专门从事将来自异构系统的原始数据抽取出来经过清洗加工后再装载入目标仓库的过程自动化脚本编程工作。典型活动描述为: - 利用SSIS包定义映射关系读取CSV文件导入至临时格内初步整理字段格式。 - 实施数据质量校验逻辑剔除异常值填补缺失项保证最终入库前达到预期标准。 ```xml <DataFlowTask Name="Import CSV to Temp Table"> <FlatFileSource ConnectionString="..." /> <DerivedColumns> <!-- Add derived columns here --> </DerivedColumns> <OleDbDestination ConnectionString="Initial Catalog=TempDb;..." TableName="[dbo].[StagingTable]" /> </DataFlowTask> ```
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值