帮助SQL查询游标 [英] Help with SQL Query Cursor

问题描述

HI，



我有一个存储过程，我试图使用Cursor从所有数据库中选择所有表：



它没有按预期工作。请帮助：

I have a Stored procedure where I am trying to select all the tables from all the databases using Cursor:

It is not working as expected. Please help:

ALTER PROCEDURE [dbo].[ReseedClientDatabase]
	
AS
BEGIN
	
DECLARE @DBName VARCHAR(256)
DECLARE @TBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
 SET @getDBName = CURSOR FOR
 SELECT name
FROM Master.dbo.SysDatabases where name like 'L001%' OR name like 'T0%'
DECLARE @GetTbName CURSOR


CREATE TABLE #TmpTable (DBName VARCHAR(256),TBname VARCHAR(256)
)
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
set @GetTbName=CURSOR FOR 
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG=@DBName
OPEN @GetTbName
FETCH NEXT 
FROM @GetTbName into @TBName
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @varSQL = 'USE ' + @DBName + ';
 INSERT INTO #TmpTable
 SELECT '''+ @DBName + ''' AS DBName,
 SELECT '''+ @TBName + ''' AS TBName'
 EXEC (@varSQL)
 FETCH NEXT 
FROM @GetTbName into @TBName
END
CLOSE @GetTbName
DEALLOCATE @GetTbName



FETCH NEXT
FROM @getDBName INTO @DBName
END

 CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable


END

谢谢。

Thanks.

推荐答案

你实际上不需要使用游标，因为你在存储过程中使用它，我建议不要使用它们。我注意到你要做的事情有几个问题，所以我清理了存储过程的主体。尝试一下，如果您仍然遇到问题（特别是您遇到的问题），请告诉我。此外，个人提示，当您在这些论坛中发布内容时，尽量使用格式化和清理代码，这将使我们更容易遵循您的代码。

You actually don't need to use a cursor for this and because you are using this in a stored procedure, I would recommend not using them. I noticed a few issues with what you are trying to do, so I cleaned up the body of your stored procedure. Give this a try and let me know if you are still having issues with it (specifically what the issue is you are having). Also, a personal note, when you post things in these forums, try to format and clean your code as best as possible, it will make it a lot easier for us to follow your code.

DECLARE @DBName VARCHAR(256)
DECLARE @TBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)

DECLARE @newLine CHAR(2) = CHAR(13) + CHAR(10)

DECLARE @databases TABLE
(
	Id INT IDENTITY PRIMARY KEY,
	DatabaseName VARCHAR(1000)
);

DECLARE @tables TABLE
(
	Id INT IDENTITY PRIMARY KEY,
	TableName VARCHAR(1000)
)

INSERT INTO @databases
SELECT
	name
FROM [master].dbo.SysDatabases
WHERE
	name like 'L001%'
OR
	name like 'T0%'
 

CREATE TABLE #TmpTable (DBName VARCHAR(256),TBname VARCHAR(256))

DECLARE @databaseIndex INT = 1, @databaseCount INT

SELECT
	@databaseCount = COUNT(*)
FROM @databases

WHILE @databaseIndex <= @databaseCount
BEGIN
	SELECT
		@DBName = DatabaseName
	FROM @databases
	WHERE Id = @databaseIndex

	INSERT INTO @tables
	SELECT
		TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE
		TABLE_TYPE = 'BASE TABLE'
	AND
		TABLE_CATALOG = @DBName

	DECLARE @tableIndex INT = 1, @tableCount INT
	
	SELECT
		@tableCount = COUNT(*)
	FROM @tables

	WHILE @tableIndex <= @tableCount
	BEGIN
		SELECT
			@TBName = TableName
		FROM @tables
		WHERE Id = @tableIndex

		SET @varSQL = 'INSERT INTO #TmpTable' + @newLine +
			'SELECT '''+ @DBName + ''' AS DBName,' + @newLine +
			'SELECT '''+ @TBName + ''' AS TBName'
 
		EXEC (@varSQL)
	END
END

SELECT *
FROM #TmpTable
DROP TABLE #TmpTable

这篇关于帮助SQL查询游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！