I have created a cursor which iterates through all the databases and displays the 1 record per database.
I would like the records to be inserted into 1 table where I can view it. The query may change which is why I don't want to create the table structure for a specific query and insert it. I wanted to use the "select into" clause but that will fail on the second time the cursor runs
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR SELECT name FROM #DBNAME
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'use [' + @DB_Name + '] Select '''+ @DB_Name + ''' ,'+
--Enter query below
'* from authentication where username like ''%clair@indicater%'' and password = ''Rohan2410'''
-- print @Command
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
You should better use INSERT INTO ... instead of SELECT INTO, something like this:
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR SELECT name FROM #DBNAME
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'use [' + @DB_Name + ']
IF OBJECT_ID(''tempdb..##output'') IS NULL
BEGIN
SELECT NULL AS DB_Name,*
INTO ##output
FROM authentication WHERE 1=0
END
INSERT INTO ##output
Select '''+ @DB_Name + ''' ,'+
--Enter query below
'* from authentication where username like ''%clair@indicater%'' and password = ''Rohan2410'''
-- print @Command
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
SELECT * FROM ##output
DROP TABLE ##output
Basically, on the first cursor iteration we will create an empty temp table with the correct structure. Then we just insert into that temp table.