July 25, 2010 at 10:39 pm
I have a procedure which is executing the following code for every database inside a cusror.
SELECT Master,serverproperty('servername'),a.*, b.GenTableCode, d.FirstName, d.SurName , e.Firstname as ClientFirstname, e.Surname as ClientSurname, f.*, g.ReportTitle
FROM master..AuditTrail a
LEFT JOIN master..GenTable b ON a.TableNumber = b.TableNumber
LEFT JOIN master..GenUser c ON a.UserNumber = c.UserNumber
LEFT JOIN master..GenPerson d ON c.GenUserID = d.GenPersonID
LEFT JOIN master..Client e ON a.ClientID = e.ClientID
LEFT JOIN master..AuditTrailReportParameters f ON a.SequenceID = f.AuditSequenceID AND a.RowID = f.ReportRowID
LEFT JOIN master..GenReports g ON a.RowID = g.ReportID
WHERE b.GenTableCode = 'GenReports'
And a.ActionDateTime>'27 Nov 2004 00:00:00' And a.ActionDateTime <'3 Dec 2010 21:00:00' -- Amend accordingly
ORDER BY a.ActionDateTime
I want to avoid cursor and the above code should be executed for every database (including system databases)..
In the above example, this was executing for Master db..
any help would be highly appreciated..
TIA
July 25, 2010 at 11:01 pm
You can create a stored procedure for this code and use sp_MSforeachdb to execute it against each database.
Example:
EXEC SP_MSFOREACHDB @COMMAND1 ='SELECT DB_NAME(),DATABASEPROPERTYEX([?],'IsAutoClose''
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 25, 2010 at 11:09 pm
Thanks for the reply. But this wont solve my work as my code is filtering databases in where clause while selecting the cursor..
declare CURSOR FOR SELECT [name]
FROM master..sysdatabases
WHERE [name] Like 'xyz_%' and [Name] NOT IN ('abcd', 'pqr')
July 25, 2010 at 11:19 pm
It can be achieved using the same logic. Here is an example
EXEC SP_MSFOREACHDB @COMMAND1 ='use [?]
if db_name()like ''%temp%''
begin
exec sp_helpfile
end
'
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 25, 2010 at 11:22 pm
No luck with your code..can you please generate the code which was mentioned in my first mail with your logic...
July 26, 2010 at 12:44 pm
Adiga (7/25/2010)
You can create a stored procedure for this code and use sp_MSforeachdb to execute it against each database.Example:
EXEC SP_MSFOREACHDB @COMMAND1 ='SELECT DB_NAME(),DATABASEPROPERTYEX([?],'IsAutoClose''
That doesn't actually avoid a cursor. MSForEachDB has a huge cursor buried in it's code.
--Jeff Moden
July 26, 2010 at 12:45 pm
NewBeeSQL (7/25/2010)
No luck with your code..can you please generate the code which was mentioned in my first mail with your logic...
This is one of those places where a cursor isn't actually a bad thing.
--Jeff Moden
July 26, 2010 at 12:58 pm
You can't really apply set-based logic at the database level because they're not rows in a table, they are full databases. Even if you could, the engine would still have to run your code against each database individually. There would be no performance bonus to such a construct. SP_MSFOREACHDB is a great example.
July 26, 2010 at 11:43 pm
Jeff / Jvanderberg...Thanks a lot for your explantions...
July 27, 2010 at 2:08 pm
NewBeeSQL (7/26/2010)
Jeff / Jvanderberg...Thanks a lot for your explantions...
You bet. Thank you for the feedback.
As a side bar... "one" of the exceptions to what JVanderberg and I have been talking about is the use of synonymns, "snynonymn views", and partitioned views. It takes a bit of work to set them up (actually, not much if you're good with dynamic SQL) but they can and do simplify some rather complex code across databases. As with everything else, "It Depends".
--Jeff Moden
July 28, 2010 at 2:17 am
unless you have millions of dabases to loop through... what's wrong with a cursor?
It's always better to understand why cursors aren't optimal and then you can decide if it is appropriate to use them.
July 28, 2010 at 4:20 am
No need for a cursor:
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'SELECT ''' + name + ''',serverproperty(''servername''),a.*, b.GenTableCode, d.FirstName, d.SurName , e.Firstname as ClientFirstname, e.Surname as ClientSurname, f.*, g.ReportTitle
FROM master..AuditTrail a
LEFT JOIN [' + name + ']..GenTable b ON a.TableNumber = b.TableNumber
LEFT JOIN [' + name + ']..GenUser c ON a.UserNumber = c.UserNumber
LEFT JOIN [' + name + ']..GenPerson d ON c.GenUserID = d.GenPersonID
LEFT JOIN [' + name + ']..Client e ON a.ClientID = e.ClientID
LEFT JOIN [' + name + ']..AuditTrailReportParameters f ON a.SequenceID = f.AuditSequenceID AND a.RowID = f.ReportRowID
LEFT JOIN [' + name + ']..GenReports g ON a.RowID = g.ReportID
WHERE b.GenTableCode = ''GenReports''
And a.ActionDateTime>''27 Nov 2004 00:00:00'' And a.ActionDateTime <''3 Dec 2010 21:00:00'' -- Amend accordingly
ORDER BY a.ActionDateTime;
'
FROM sys.databases
WHERE state = 0
ORDER BY name
EXEC(@SQL)
Chris
July 28, 2010 at 9:54 am
That is a really creative way of avoiding using a cursor, but is it really getting around any of the problems with cursors? The primary problems with cursors are locking and the fact that the engine can't really optimize a cursor. If you're performing operations on a per-database level, the first problem doesn't apply and the second one is unavoidable. You're not going to cause lock contention on a system view, and the system's going to have to come up with a query plan for each iteration no matter what. Your example, while highly creative, just generates a bunch of SQL scripts, each of which needs its own query plan. You haven't really gotten around anything, you've just generated an overly-complicated query with no real performance benefit. Don't get me wrong: that's some great outside-of-the-box thinking, I just think it's a little misplaced. You'd be far better off optimizing something that can benefit more from it.
--J
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply