Click here to Skip to main content
15,039,149 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
HI,

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:
SQL
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.
Posted
Updated 1-Jul-15 6:32am
v3
Comments
Andy Lanng 1-Jul-15 11:21am
   
We don't know how you "expect" this to work and we don't know in what way it is "not working as expected".
Please help us help you and explain what is mean by these terms

1 solution

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.

SQL
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
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900