Solved

run sql script against database from management studio

Posted on 2013-12-03
1
346 Views
Last Modified: 2013-12-12
Hi,

I have a number of sql databases in an instance and need to run a sql script on the operating system against each database;
I have the query below which runs against each database but when i add a sql command like sqlcmd -i c:\upgrade.sql against each database it won't work;

EXEC master..sp_MSforeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
                print ''Processing database ?''
         
                sqlcmd -i c:\upgrade.sql
END
'

how can i run my upgrade scrip agains the 50 database in my instance from sql management studio or some other better way ?
0
Comment
Question by:hraja77
1 Comment
 
LVL 9

Accepted Solution

by:
BlueYonder earned 500 total points
ID: 39692784
Here is some of the code you will have to fill in the  t-sql in the while loop




--Check if #TableNames exists

PRINT 'Job Start Time: ' + convert(varchar(30), getdate(), 109)

IF OBJECT_ID('tempdb..#TableNames') IS NOT NULL
BEGIN
      DROP TABLE #TableNames
END

--CREATE TEMP TABLE TO STORE TABLE NAMES AND NUMBER OF MISMATCH RECORDS
CREATE TABLE #TableNames(
      table_id INT PRIMARY KEY IDENTITY
      , table_name varchar(100)
)

--INSERT TABLE NAMES
INSERT INTO #TableNames(table_name)
SELECT
      name
FROM
      sys.databases
WHERE
      --database_id = 13
      database_id > 6
ORDER BY 1

DECLARE @currCount int
SELECT @currCount = COUNT(*) FROM #TableNames

WHILE(@currCount > 0)
BEGIN
      --fill in with t-sql for each table
END


--delete temp table
DROP TABLE #TableNames



PRINT 'Job End Time: ' + convert(varchar(30), getdate(), 109)

--DATABASE FILES
--SELECT * from sys.database_files
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

914 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

22 Experts available now in Live!

Get 1:1 Help Now