April 7, 2009 at 8:50 am
Ok I have a server with about 400 databases on it.
These databases house a series of customer applications (the same app) and use four databases to do it.
One database is a demo/testing/training database
One database is the production database
One database stores customer centric localization information
One database is used to queue up print jobs for reports
I need to deploy a patch to the demo and data databases, and skip the localization and print job databases.
What I had wanted to do was write up a function to peek into the sys.databases on each database and if a specific table was there, it was either demo or data (copies of each other) else we skip it.
So I keyed this all up.
CREATE FUNCTION dbo.is_this_datademo (@db sysname)
RETURNS @present TABLE (retvalue int)
AS
BEGIN
DECLARE @cmd nvarchar(max)
declare @params nvarchar(max)
declare @reccnt int
set @cmd = 'declare @recs int;select @recs = count(*) from [' + @db + '].sys.tables where [name] = ''Company'';select @recs'
set @params = '@reccnt int OUTPUT'
exec @reccnt = sp_executesql @cmd,@params, @reccnt = @reccnt OUTPUT
if @reccnt > 0 INSERT INTO @present VALUES(1) ELSE INSERT INTO @present VALUES(0)
RETURN
END
The idea being to declare a cursor for the following query and process each database in turn.
SELECT
dbname = [name]
FROM
sys.databasea a CROSS APPLY dbo.is_this_datademo(a.[name]) b
WHERE
b.retvalue = 1
But of course I run into
Msg 557, Level 16, State 2, Line 1
Only functions and extended stored procedures can be executed from within a function.
When I do it...
Other than stepping through each database with an actual cursor, is there an easy way to problem them all with a simple select?
April 7, 2009 at 9:24 am
This will give you a list of the databases that have that table:
declare @SQL nvarchar(max);
select @SQL = coalesce(
@SQL + ' union all select ''' + name + ''' as db from [' + name + '].sys.tables where name = ''Company''',
'select ''' + name + ''' as db from [' + name + '].sys.tables where name = ''Company''')
from sys.databases;
print @SQL;
exec (@SQL);
You can modify that to insert into a temp table, or to run a script in each of them, or to build a script, or whatever else you need.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 9:29 am
That helps a ton...
That coalesce function... what doesn't it do? 🙂
although I wound up with this... which seems to run faster.
CREATE TABLE #process_dbs(
dbname sysname,
alreadyprocessed int)
sp_msforeachdb 'USE [?];declare @reccnt int;select @reccnt = count(*) from sys.tables where [name] = ''Company'';if @reccnt > 0 INSERT INTO #process_dbs VALUES(''?'',0)'
April 7, 2009 at 9:35 am
Either one should be pretty darn fast.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply