时间戳

首页 > 解决方案 > 从 TSQL 中的表列表中检查表是否为空

sql - 从 TSQL 中的表列表中检查表是否为空

问题描述

我想创建一个表列表并检查列表中的每个表是否不为空（即必须填充）。下面是逻辑的伪代码，但需要在 TSQL 中。

伪代码：

DECLARE @ctr INT = 0

// list of table objects, how to do in TSQL
DECLARE @sourceTables = {
    src.fusion_sec_assigned_workgroup,
    src.fusion_sec_user_account,
    src.fusion_sec_user_in_organisation,
    src.BULL_OHMS_WORKGROUPS,
    src.fusion_rep_extension_request_reason,
    src.fusion_rep_extension_request_trade,
    src.fusion_rep_job_ext_request_reason,
    src.fusion_rep_job_ext_request_trade,
    src.bull_rep_audit_adhoc_jobs,
    src.BULL_REP_AUDIT_JOB_NONSORS,
    src.bull_REP_AUDIT_JOB_SORS,
    src.BULL_REP_REASON_CODES
}

WHILE (@ctr < sourceTables.length)
BEGIN
    EXEC tSQLt.AssertNonEmptyTable 'sourceTables[ctr]'
    SET @ctr = @ctr + 1
END

编辑：改进的尝试

DROP TABLE IF EXISTS #SourceTables
CREATE TABLE #SourceTables
(
    Id INT,
    TableName NVARCHAR(MAX)
)
INSERT INTO #SourceTables
    VALUES
        (1, 'src.fusion_sec_assigned_workgroup'),
        (2, 'src.fusion_sec_user_account'),
        (3, 'src.fusion_sec_user_in_organisation'),
        (4, 'src.BULL_OHMS_WORKGROUPS'),
        (5, 'src.fusion_rep_extension_request_reason'),
        (6, 'src.fusion_rep_extension_request_trade'),
        (7, 'src.fusion_rep_job_ext_request_reason'),
        (8, 'src.fusion_rep_job_ext_request_trade'),
        (9, 'src.bull_rep_audit_adhoc_jobs'),
        (10, 'src.BULL_REP_AUDIT_JOB_NONSORS'),
        (11, 'src.bull_REP_AUDIT_JOB_SORS'),
        (12, 'src.BULL_REP_REASON_CODES')

DECLARE @ctr INT = 1
DECLARE @length INT = (SELECT COUNT(*) FROM #SourceTables)
WHILE (@ctr <= @length)
BEGIN
    PRINT @ctr
    EXEC tSQLt.AssertNonEmptyTable '' -- how to refer to a table name per iteration (in C# something like SourceTables[ctr])
    SET @ctr = @ctr + 1
END

谢谢你的帮助。

标签： sqlsql-servertsql

解决方案

您将需要使用Dynamic SQL查询。sp_executesql

您没有指定所需的输出，我修改了#SourceTables添加计数列来记录该表中的行数。

CREATE TABLE #SourceTables
(
    Id INT,
    TableName NVARCHAR(MAX),
    TableCount INT                   -- Added this
)
INSERT INTO #SourceTables (Id, TableName)
    VALUES
        (1, 'src.fusion_sec_assigned_workgroup'),
        (2, 'src.fusion_sec_user_account'),
        (3, 'src.fusion_sec_user_in_organisation'),
        (4, 'src.BULL_OHMS_WORKGROUPS'),
        (5, 'src.fusion_rep_extension_request_reason'),
        (6, 'src.fusion_rep_extension_request_trade'),
        (7, 'src.fusion_rep_job_ext_request_reason'),
        (8, 'src.fusion_rep_job_ext_request_trade'),
        (9, 'src.bull_rep_audit_adhoc_jobs'),
        (10, 'src.BULL_REP_AUDIT_JOB_NONSORS'),
        (11, 'src.bull_REP_AUDIT_JOB_SORS'),
        (12, 'src.BULL_REP_REASON_CODES')


DECLARE @SQL    NVARCHAR(MAX)

-- Form the dynamic sql query
select  @SQL    = ISNULL(@SQL + ';' + char(13), '')
                + 'UPDATE #SourceTables SET TableCount = (SELECT COUNT(*) FROM ' + TableName + ') WHERE TableName = ''' + TableName + ''''
from    #SourceTables

-- print out for verification    
print   @SQL

-- execute the query
exec    sp_executesql @SQL

select  *
from    #SourceTables

推荐阅读