Retrieve data from same table in all server databases

Question

I have SQL Server 2012 with DB1 , DB2 , Db3 ...> DB50

If I need to view databases in server I use

SELECT * FROM sys.databases

All databases have same table Saves except for five databases that do not have this table.

I need to know which databases have table Saves - for this I use

sp_msforeachdb "SELECT '?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE '%saves%'"

How do I retrieve data from table Saves from all databases if found?

I use one by one and it takes a lot of time to save data like

select * from db1.dbo.Saves

and save data then back to search

select * from db2.dbo.Saves

and save data then back to search

select * from db3.dbo.Saves

I need a SQL command like :

Select * from Saves in  sys.databases  if found

score 0 · Answer 1 · answered Sep 11 '16 at 01:19

Enjoy ...

DECLARE @name  VARCHAR(50), -- database name  
        @Query  NVARCHAR(500) -- My Query

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN 
set @Query ='USE  '+@name+' 
            Go 
            if OBJECT_ID(''['+@name+'].[dbo].[saves]'') is not null
            begin
                select * from ['+@name+'].[dbo].[saves]
            end
            else
            begin
                select ''table saves does not exist into database: '+@name+''' 
            end'

/* the next line for resloving error : Incorrect syntax near 'Go'*/
SET @Query = 'EXEC (''' + REPLACE(REPLACE(@Query, '''', ''''''), 'GO', ''');     EXEC(''') + ''');'--Just add this one line.

exec sp_executesql @Query

FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

thanks very much it's working fine but didn't show what database name — محمد عبدالغفار الفقى, Sep 12 '16 at 00:36
Can ad name for database that found & Working fine from MSSQL management but when using from web site page i design it to run sql command work with other command but not this code — محمد عبدالغفار الفقى, Sep 12 '16 at 00:53

score 0 · Answer 2 · answered Sep 11 '16 at 18:11

0

You can use below query for result.

EXEC sp_msforeachdb '
USE [?];
IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.TABLES AS T WHERE T.TABLE_NAME=''Saves'')
BEGIN
DECLARE @QUERY VARCHAR(MAX);
SELECT @QUERY = COALESCE(@QUERY+'';
SELECT ''''?'''' AS DBNAME; SELECT ''''?'''' AS DBNAME,* FROM [''+T.TABLE_CATALOG+''].''+''[''+T.TABLE_SCHEMA+'']''+''.[''+T.TABLE_NAME+'']'',''
SELECT ''''?'''' AS DBNAME;
SELECT ''''?'''' AS DBNAME,* FROM [''+T.TABLE_CATALOG+''].''+''[''+T.TABLE_SCHEMA+'']''+''.[''+T.TABLE_NAME+'']'')
FROM [?].INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME = ''Saves''
EXEC (@QUERY);
END
';

`

answered Sep 11 '16 at 18:11

Ajay Dwivedi

328
2
14

thanks very much it's working fine and show the name of the database who it his table thanks again – محمد عبدالغفار الفقى Sep 12 '16 at 00:37
Working fine from MSSQL management but when using from web site page i design it to run sql command work with other command but not this code – محمد عبدالغفار الفقى Sep 12 '16 at 00:53
( retrieve first row only from results ) in web executing – محمد عبدالغفار الفقى Sep 12 '16 at 01:01

Retrieve data from same table in all server databases

2 Answers2