Solved

run sql script against database from management studio

Posted on 2013-12-03
1
347 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

786 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