SQL Query against multiple databases

vpnsol123
vpnsol123 used Ask the Experts™
on
I need to write a query, that will be used as a view to select all columns from the same named table in each of my databases into a single result panel.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If this is Sql Server then you can use multi-part names that are capable of referencing objects from different schemas, different databases and even different servers.

See this link for more info...   https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql

Author

Commented:
Thank you for the above.  What I am looking to do is to write a single query the will read the database names out of the sys_databases table and run the query against each of them.  Sorry, that was not clear at all in my initial post.
You will need to use the System Catalog Views to access data contained in the master database.
SharathData Engineer
Commented:
try this. Replace Your_Table with your actual table name
DECLARE @Result TABLE (DB_NAME VARCHAR(100), TABLE_NAME VARCHAR(100), COLUMN_NAME VARCHAR(100))
INSERT @Result
EXEC sp_Msforeachdb "USE ?; SELECT '?' DB_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Your_Table'"
SELECT * FROM @Result

Open in new window

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial