s_hausen
asked on
create or update table(s) in multiple databases
Hi,
I've more than 800 databases in my sql server 2008 R2. Often, I need to update or create a table or multiple tables in my databases and excluding the ones i do not want to create or update the table(s) in it. I am trying to use the below code.
But when i run it, sql server only create or update the table(s) in the selected database of the sql server drop down. Image is attached, so in the scenario of this question case, it create the table only in DB3, not in all the databases. Any feedback, comment or suggestion would be deeply appreciated.
SQLWindow.jpg
I've more than 800 databases in my sql server 2008 R2. Often, I need to update or create a table or multiple tables in my databases and excluding the ones i do not want to create or update the table(s) in it. I am trying to use the below code.
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''DB3'')
BEGIN
create table dbo.tblPhys
(
name VARCHAR(50),
physical_name VARCHAR(500),
state BIT,
size INT
)
END'But when i run it, sql server only create or update the table(s) in the selected database of the sql server drop down. Image is attached, so in the scenario of this question case, it create the table only in DB3, not in all the databases. Any feedback, comment or suggestion would be deeply appreciated.
SQLWindow.jpg
TempDBA
sp_MSforeachdb should create it on all the databases. Have you checked it correctly because I tried it on 3 user databases removing system databases and it created on all. When you run the script, what is the output in the output window?
ASKER
it does create a table but only in the selected drop down database , so in this case only in DB3. in the script i've excluded it. after running this code i get this error.
even i delete the table and run the script, it again create's the table in DB3 and give me the same error..
Msg 2714, Level 16, State 6, Line 4
There is already an object named 'tblPhys' in the database.even i delete the table and run the script, it again create's the table in DB3 and give me the same error..
Looks like your database includes space at the end. Try running below script
select (db_name() + 'A')
select (db_name() + 'A')
ASKER
i ran it as mentioned, and this time i selected the database clientsInfo. So in this case, script only created the table inside ClientsInfo(even it is excluded in the script)
and after creating table in excluded database, it generates an error.
is there a way, i ignore to select any database from sql server database drop down?? after that the script might work.
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''Institute'',''TDATA'',''ClientsInfo'',''paymentnet'')
BEGIN
create table dbo.tblPhys
( name VARCHAR(50),
physical_name VARCHAR(500),
state BIT,
size INT )
END'
select (db_name() + 'A')and after creating table in excluded database, it generates an error.
Msg 2714, Level 16, State 6, Line 3
There is already an object named 'tblPhys' in the database.is there a way, i ignore to select any database from sql server database drop down?? after that the script might work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i made little change in the code and it works like a charm now.
thanks for everyone's input. code is as under:
thanks for everyone's input. code is as under:
EXEC sp_MSforeachdb 'IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''Institute'',''ReportServerTempDB'',''TSQLFundamentals2008'')
BEGIN
USE [?] --<<-- add this
create table tblPhys
(
name VARCHAR(50),
physical_name VARCHAR(500),
state BIT,
size INT
)
END'