Solved

run sql script against database from management studio

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

760 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

20 Experts available now in Live!

Get 1:1 Help Now