0

I have cloud server where I've hosted a web application for my customers. Each customer has a different SQL database and website in IIS. Whenever I want to execute a sql query to update something, I have to do this manually in each database. There are almost 50 databases and it takes around an hour in executing single query each time. Can someone provide me a tool or way by which I just select all the database at once and execute that query simply?

7
  • What is your database? All customer's database are located in single server? Commented May 29, 2019 at 7:14
  • 1
    What about writing a script that does it for you? Commented May 29, 2019 at 8:03
  • Well I m not expert in script writing, it would be very helpful if someone assist me in this regard. I have single server on which all the databases are hosted (approx. 50). Commented May 29, 2019 at 8:14
  • Which RDBMD you are using? Commented May 29, 2019 at 8:23
  • Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product (and for different vendors the term "database" means completely different things). Please add a tag for the database product you are using postgresql, oracle, sql-server, db2, ... Commented May 29, 2019 at 9:40

1 Answer 1

1

If I guess you have all databases are in same structure and every time you run script to update something, the script basically same and you just run that same script one by one for each customer.

If the above case is true, you can use CURSOR to produce a Loop between your all databases and Execute necessary script to serve your purpose.

Note: This is not the solution, Just Idea.

--The first step will be creating a Table variable 
--where you will INSERT all your database names 
--for a further loop as below- 

DECLARE @DbName VARCHAR(200)
DECLARE @DatabaseList TABLE (DbName VARCHAR(200))

INSERT INTO @DatabaseList (DbName) VALUES('db_name_1')
INSERT INTO @DatabaseList (DbName) VALUES('db_name_2')
--.......................
INSERT INTO @DatabaseList (DbName) VALUES('db_name_50')

--Now you can use CURSOR to generate the loop 
--and execute your required script as shown below

DECLARE db_cursor CURSOR FOR 
SELECT DbName FROM @DatabaseList

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @DbName  

WHILE @@FETCH_STATUS = 0  
BEGIN  

    --HERE You need to write your script That you
    --Execute for all your database. I have added
    --a sample script where I guess you updating 
    --certain tables in your all database WHERE ID = 1

    -- You can see the Database Name inserted in the 
    -- Script Dynamically from the Loop

    EXEC ('UPDATE '+@DbName+'.dbo.<Your_table_Name_Here> 
           WHERE ID=1')

    --END OF Dynamic Part

    FETCH NEXT FROM db_cursor INTO @DbName 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.