run sql script against database from management studio

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 ?
LVL 1
hraja77Asked:
Who is Participating?
 
BlueYonderConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.