Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
przemek_pbi
Regular Visitor

Use a list of databases from one query into another query cursor

Hi All,

 

I've got hundreds of databases with identical tables structure and tens of queries to run against these dbs. To avoid loading list of db for each query cursor, I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries. The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it.

 

DECLARE
    @db VARCHAR(128),
	@query VARCHAR(MAX), 
    @sql VARCHAR(MAX)

SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''

CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128))

DECLARE db_cursor CURSOR FOR

--I want to replace this line with a already loaded recordset 'DBs'
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #Tables

DROP TABLE #Tables

Regards,

Przemek

3 REPLIES 3
przemek_pbi
Regular Visitor

Hi All,

 

I've got hundreds of databases with identical tables structure and tens of queries to run against these dbs. To avoid loading list of db for each query cursor, I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries. The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it.

 

DECLARE
    @db VARCHAR(128),
	@query VARCHAR(MAX), 
    @sql VARCHAR(MAX)

SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''

CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128))

DECLARE db_cursor CURSOR FOR

--I want to replace this line with a already loaded recordset 'DBs'
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #Tables

DROP TABLE #Tables

Regards,

Przemek

v-juanli-msft
Community Support
Community Support

Hi @przemek_pbi

For this requirement,

"I've created a seperate query in PBI - DBs - that keeps the list of dbs which I need to incorporate in my SQL queries."

you could consider to disable"parallel loading of tables" so that power bi would not load mutiple queries in parallel.

 

"The queries are too complicated for me to transform them into DAX so I'd like to keep them in pure SQL. Please see the below example and advise how to do it."

From above I'm not clear which situation you meet,

did you import data from database and need filter the columns or rows which needs to be load in,

or, have you alreadly load data in, then you need to create measures or columns,a new table based on your dataset in power bi?

 

If it is the first scenario, also if your database is a sql database, you could write sql statement to define which data should be load in.

Reference here

https://support.office.com/en-us/article/Import-Data-from-Database-using-Native-Database-Query-Power...

 

if it the second one,  for the bold line, it can be transformed to DAX formula as below

SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')
New table
new table = SUMMARIZE(FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}),[name])

Or column
Column = CALCULATE(MAX([name]),FILTER(ALL(Table1),NOT[name] IN {"master","model","msdb","tempdb"}))

 

 

Best Regards

Maggie

 

 

Hi Maggie,

 

In my report there're tens of queries, each query needs to be run in each database (there hundreds of them). What I need here is to use the once imported list of databases (DBs) to run my queries in without the necessity to import the list from SQL in each query. 

 

 

I'd imagine my code would look like but PBI doesn't allow that.

 

DECLARE
    @db VARCHAR(128),
	@query VARCHAR(MAX), 
    @sql VARCHAR(MAX)

SET @query = 'SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'''

CREATE TABLE #Tables (DBNAME VARCHAR(128), TABLE_NAME VARCHAR(128))

DECLARE db_cursor CURSOR FOR

SELECT name FROM DBs[name]

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @db

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @sql =' USE '+@db+'; INSERT INTO #Tables '+@query+''
    FETCH NEXT FROM db_cursor INTO @db
    EXEC (@sql)
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT * FROM #Tables

DROP TABLE #Tables

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.