June 4, 2009 at 9:00 am
Trying to refine a SP query, currently using sp_MSforeachDB but I'm wanting to select specific DBs for the query to run on.
Here's a break down of my current query.
CREATE PROCEDURE [dbo].[spRCM]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
create table #result_temp (database_name varchar(20),abbreviation varchar(30), txtdesc varchar(30))
EXEC sp_MSforeachdb @command1='
USE [?];
Any help would be most appreciated.
IF object_id (''dbo.Reimbursement_Comments'') IS NOT NULL
BEGIN
INSERT INTO #result_temp SELECT ''?'', Abbreviation, [description] from Reimbursement_Comments where Abbreviation = [description]
END
'
SET NOCOUNT OFF
select * from #result_temp
SET NOCOUNT ON
drop table #result_temp
END
June 4, 2009 at 10:35 am
Your code runs perfectly, what's the matter with it?
If you want to run it only against a specifica DB, you could try to add the @db param, like this:
ALTER PROCEDURE [dbo].[spRCM] @db SYSNAME = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @cmd NVARCHAR(4000)
CREATE TABLE #result_temp
(
database_name VARCHAR(20),
abbreviation VARCHAR(30),
txtdesc VARCHAR(30)
)
SET @cmd = 'USE [?];'
IF @db IS NOT NULL
SET @cmd = @cmd + 'IF DB_ID() = DB_ID(''' + @db + ''') ' + CHAR(13)
+ CHAR(10) + 'BEGIN'
SET @cmd = @cmd
+ '
IF object_id (''dbo.Reimbursement_Comments'') IS NOT NULL
BEGIN
INSERT INTO #result_temp SELECT ''?'', Abbreviation, [description] from Reimbursement_Comments where Abbreviation = [description]
END
'
IF @db IS NOT NULL
SET @cmd = @cmd + CHAR(13) + CHAR(10) + 'END'
EXEC sp_MSforeachdb @command1 = @cmd
SET NOCOUNT OFF
SELECT *
FROM #result_temp
SET NOCOUNT ON
DROP TABLE #result_temp
END
And then you could run EXEC spRCM 'whateverDBNameILike'
But I don't know if this is what you want.
Regards
Gianluca
-- Gianluca Sartori
June 4, 2009 at 10:41 am
That's exactly what I'm wanting to do. I'm kind of new to SQL so I was trying to figure out a way to select specific DBs. I have some DBs that are no longer active and I'm wanting to exclude them from the query so that way it returns DBs that are currently active.
June 4, 2009 at 3:50 pm
What defines whether a database is active or not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2009 at 6:41 am
Not active refers to DBs for clients that are no longer customers of ours along with training and test DBs. Each of our clients get their own DB. So when I run the query I'm just wanting information on clients that are currently customers of ours.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply