理解动态SQL

Question

在我试图理解动态SQL的过程中，我尝试通过动态插入sys.tables的每个表中的行数来开始simple。

这是我的密码：

SELECT 
    Name, ROW_NUMBER() OVER (ORDER BY NEWID() ) AS SomeNumb
INTO
    #Dyn
FROM 
    sys.tables

CREATE TABLE ##Results (Cnt INT)    

DECLARE @Table NVARCHAR(100)
DECLARE @Counter INT

SET @Counter = 1
SET @Table = (SELECT Name FROM #Dyn WHERE SomeNumb = @Counter)

DECLARE @Sql NVARCHAR(1000)

WHILE @Counter <= (SELECT COUNT(*) FROM #Dyn)
BEGIN
    INSERT INTO ##ResultsTable
    SELECT @Sql = 'SELECT COUNT(*) AS Cnt FROM #Dyn WHERE 
    Name = ' + @Table + 'AND SomeNumb = ' + @Counter
    EXECUTE (@Sql)

    SET @Counter = @Counter + 1
    SET @Sql = ''
END

SELECT * FROM ##ResultsTable

唯一的好处是不要出错。虽然这可能会给我一些方向。我知道我的ResultsTable存在范围问题，但我认为使用##而不是#可以解决这个问题。

任何指示都将不胜感激。

Answer 1

这里有一个经过审查的脚本。

IF OBJECT_ID('tempdb..#Dyn') IS NOT NULL
    DROP TABLE #Dyn

SELECT Name, ROW_NUMBER() OVER (ORDER BY NEWID() ) AS SomeNumb
INTO #Dyn
FROM sys.tables

IF OBJECT_ID('tempdb..#ResultsTable') IS NOT NULL
    DROP TABLE #ResultsTable

CREATE TABLE #ResultsTable (TotalRowCount INT)

DECLARE @Counter INT = 1
DECLARE @Sql NVARCHAR(MAX)

WHILE @Counter <= (SELECT COUNT(*) FROM #Dyn)
BEGIN

    DECLARE @Table NVARCHAR(100) = (SELECT Name FROM #Dyn WHERE SomeNumb = @Counter)

    SELECT @Sql = '
        INSERT INTO #ResultsTable (TotalRowCount)
        SELECT COUNT(*) AS Cnt 
        FROM #Dyn 
        WHERE Name = ''' + @Table + ''' AND SomeNumb = ' + CONVERT(NVARCHAR(10), @Counter)

    EXECUTE (@Sql)

    SET @Counter += 1

END


SELECT * FROM #ResultsTable

有几件事要提：

• 您可以在EXEC之外创建一个临时表，并将其插入动态SQL中。
• COUNT()表的#Dyn总是返回1，因为只有1条记录具有特定的表名。
• 在动态SQL上打印varchar值(如@Table)时，需要添加额外的引号：
• 在构建动态SQL (如VARCHAR )时，需要将所有非文字值转换为NVARCHAR或@Counter。

编辑：如果您想要对每个表进行实际计数，那么您不需要查询#Dyn！

SELECT @Sql = '
    INSERT INTO #ResultsTable (TotalRowCount)
    SELECT COUNT(*) AS Cnt 
    FROM ' + QUOTENAME(@Table) +

EXECUTE (@Sql)

Answer 2

一种方法不是使用CURSOR或WHILE循环，而是使用sys.tables、sys.schemas和FOR XML PATH。

DECLARE @SQL nvarchar(MAX);
SET @SQL = (SELECT N'SELECT ''' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.name) +''' AS ObjectName, COUNT(*) AS [RowCount]' + NCHAR(10) +
                   N'FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N';' + NCHAR(10)
            FROM sys.tables t
                 JOIN sys.schemas s ON t.SCHEMA_ID = s.SCHEMA_ID
            FOR XML PATH(N''))

PRINT @SQL;
CREATE TABLE #Cnt (ObjectName sysname, [RowCount] int);
INSERT INTO #Cnt (ObjectName, [RowCount])
EXEC sp_executesql @SQL;

SELECT *
FROM #cnt;

DROP TABLE #cnt;

或者，您可以使用无文档化的过程sp_msforeachtable。

CREATE TABLE #Cnt (ObjectName sysname, [RowCount] int);
INSERT INTO #Cnt (ObjectName, [RowCount])
EXEC sp_msforeachtable N'SELECT ''?'' AS ObjectName, COUNT(*) AS [RowCount]
FROM ?;';

SELECT *
FROM #cnt;

DROP TABLE #cnt;

但是，如果我没有记错的话，后一种方法实际上使用了CURSOR。