代码之家 › 专栏 › 技术社区 › SQL_M

了解动态SQL

dynamic-sql sql-server-2008 sql-server

SQL_M · 技术社区 · 6 年前

在我尝试理解动态SQL的过程中,我尝试从简单开始,动态插入每个表中的行数。 sys.tables .

这是我的代码:

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 但是我想用 ## 而不是会解决这个问题。

任何提示都将受到高度赞赏。

2 回复 | 直到 6 年前

EzLo tumao kaixin 6 年前

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

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)时,需要添加其他引号:
您需要将所有非文本值转换为 VARCHAR 或 NVARCHAR 构建动态SQL时(如 @Counter )。

编辑 :如果需要每个表的实际计数,则不需要查询 #动态 你看!

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

EXECUTE (@Sql)

Larnu 6 年前

而不是使用 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;

然而,后一种方法实际上使用了 光标 如果我记错的话。