Solved

create or update table(s) in multiple databases

Posted on 2013-01-07
6
344 Views
Last Modified: 2013-01-07
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.

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'

Open in new window


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
0
Comment
Question by:s_hausen
  • 3
  • 2
6 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 38752385
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?
0
 

Author Comment

by:s_hausen
ID: 38752437
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.

Msg 2714, Level 16, State 6, Line 4
There is already an object named 'tblPhys' in the database.

Open in new window


even i delete the table and run the script, it again create's the table in DB3 and give me the same error..
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 38752500
Looks like your database includes space at the end. Try running below script
select (db_name() + 'A')
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:s_hausen
ID: 38752540
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)

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')

Open in new window


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.

Open in new window


is there a way, i ignore to select any database from sql server database drop down?? after that the script might work.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38753056
I'll touch it up fully, but if you want to, all you have to do is add the bolded line to your code:


EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''tempDB'',''model'',''msdb'',''master'',''ReportServer'',''ReportServerTempDB'',''DB3'')
    RETURN

USE [?] --<<-- add this
create table dbo.tblPhys
 (
 name VARCHAR(50),
 physical_name VARCHAR(500),
 state BIT,
 size INT
 )      
'
0
 

Author Comment

by:s_hausen
ID: 38753171
i made little change in the code and it works like a charm now.
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'

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now