September 10, 2007 at 8:17 am
I have a index maintenance SP that I would like to create in all user databases. I've tried using a cursor as well as sp_MSforeachdb but I keep running into difficulties. Is there an easy way to do this? Thanks!
Here is an example of something I've tried:
SET NOCOUNT ON
USE master
--Create temp table for use as array
Declare @tbl table ([Name] varchar(30))
--populate temp table with all user db names
Insert @tbl
SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
--SELECT * FROM @tbl
--create "array" of db names
DECLARE for_each_loop CURSOR
FOR SELECT * from @tbl
OPEN for_each_loop
DECLARE @name varchar(30)
--loop through list and perform various tasks
FETCH for_each_loop INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
--PRINT @name
exec('USE ' + @name)
-------INSERT STORED PROCEDURE CREATION SCRIPT HERE
--Destroy cursor
FETCH for_each_loop INTO @name
END
CLOSE for_each_loop
DEALLOCATE for_each_loop
GO
September 10, 2007 at 10:13 pm
Try this abbreviated version Ryan:
SET NOCOUNT ON
USE master
--create "array" of db names
DECLARE for_each_loop CURSOR
FOR SELECT name FROM sysdatabases WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Northwind', 'AdventureWorks')
OPEN for_each_loop
DECLARE @name varchar(30)
declare @cmd varchar(255)
--loop through list and perform various tasks
FETCH for_each_loop INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
--PRINT @name
exec('USE ' + @name + ' exec ' + 'sp_spaceused')
--Destroy cursor
FETCH for_each_loop INTO @name
END
CLOSE for_each_loop
DEALLOCATE for_each_loop
GO
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 11, 2007 at 7:54 am
Thanks for your response. This works great for short commands but I run into syntax errors when I paste in a large chunk of code, such as to create a store procedure. I'll guess I'll have to play around with it. I was just hoping that someone might know of a simple way to create a store procedure in all users DBs.
September 11, 2007 at 9:09 am
Out of curiosity why do you need to create the same SP in all user databases ? When a stored procedure prefixed with sp_ in the master database accomplishes the same thing. Granted every time you upgrade SQL Server you will have to recreate these local objects. But it is a price worth paying sometimes.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 11, 2007 at 9:48 am
I would like to schedule a script to perform reindexing on a regular basis. Something like this: http://www.sqlmag.com/Article/ArticleID/96059/sql_server_96059.html
The script mentioned above doesn't allow you to specify a database as an argument for the stored procedure so I believe I would need to run it in the context of the DB in which I'm reindexing. Though I guess I could be wrong about that.
September 11, 2007 at 10:55 am
In most instances:
USE databasename
go
exec sp_reindexing_procedure
is equivalent to:
USE master
go
exec databasename..sp_reindexing_procedure
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 11, 2007 at 11:03 am
Correct, but in both cases that stored proc would have to be present in the user databases where I need to reindex. What I'm trying to achieve is to create the stored proc in all user databases without having to manually switch to each of those DBs and run the create procedure script. Thanks.
September 11, 2007 at 12:45 pm
Slightly incorrect ... if the stored procedure is created in the master database and prefixed sp_ you can execute it in any database ... the databasename sets the execution context (use databasename).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 11, 2007 at 8:42 pm
Rudy,
Have you tried to run a stored procedure in the master prefixed with sp_* and worked for you? if the stored procedure is accessing local objects to the database e.g. sys.objects then it won't work. I personall couldn't get it to work in SQL 2005 unlike the case in SQL 2000
try something like this
USE master
GO
create procedure sp_GetSystemObjects
as
select name from sys.objects
go
Now try to call the sp_GetSystemObjects from master like this
EXEC master.dbo.sp_GetSystemObjects -- you get the objects within the master DB
Now try this
EXEC userdatabase.dbo.sp_GetSystemObjects -- you still get the objects within the master DB NOT the one within the userdatabase
In SQL 2005 this would be the normal behaviour, in SQL 2000, it returns the data from the correct database
Regards,
Ayman
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply