March 2, 2009 at 1:02 am
Hi All!
I have a concern for identifying a object in a table in all the databases. First let me explain my question. I had 10 databases in that i have more than 1000 objects it includes stored proc view, and trigger. In all the objects i had use With(Nolock) and Nolock option. Now i need to find out the objects in all the databases which having with(Nolock) Option in a single query. I have tried using the syscomments table to find within the databases, but i dont know how to access all the databases in a single query to find these.
Can anyone help me out to resolve this issue.....
For further clarification please mail me aprasy@hotmail.com
Thanks in advance!!
Prasanna
March 2, 2009 at 1:53 am
If I understand you...
use master
declare @db table
(
[db_name] varchar(100)
)
insert @db
select [name] from [master]..sysdatabases
where [name] not in ('master','tempdb','model','ReportServer','ReportServerTempDB')
declare @q varchar(max)
set @q= 'use ; select o.[name] from sysobjects o join syscomments c on c.id = o.id and c.text like ''%(Nolock)%'''
declare @q2 varchar(max),@d varchar(100)
declare cursdb cursor
for select [db_name] from @db
open cursdb
fetch next from cursdb into @d
while @@fetch_status >=0
begin
set @q2 = replace(@q,' ',@d)
exec(@q2)
fetch next from cursdb into @d
end
close cursdb
deallocate cursdb
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
March 2, 2009 at 7:25 am
You could also check out SQLDigger[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 3, 2009 at 12:39 am
Its giving me an error!
March 3, 2009 at 8:03 am
aprasy (3/3/2009)
Its giving me an error!
Usually it would be a good idea to include what the text of the error was!
The value set for '@q' had some characters removed.
Either use this
exec sp_MSforeachdb 'use ?; select ''?'',o.[name] from sysobjects o join syscomments c on c.id = o.id and c.text like ''%(Nolock)%'''
Or use the argument shown as the value for '@q' and change the REPLACE statement to replace the '?' character with @d.
Derek
March 3, 2009 at 11:06 pm
Its working!!!! Really much thanks for you!!
Could you tell me what the stored proc is doing in the backend "sp_MSforeachdb". Its confusing me!!!
Thanks
March 4, 2009 at 2:24 am
aprasy (3/3/2009)
Its working!!!! Really much thanks for you!!Could you tell me what the stored proc is doing in the backend "sp_MSforeachdb". Its confusing me!!!
Thanks
sp_MSforeachdb is an undocumented Microsoft-supplied system procedure that's been in SQL server since at least version 6.5.
You can find a description here[/url] (http://www.sqlservercentral.com/articles/Stored+Procedures/62868/).
Basically, it does the same as rutgersdba's script (when his is fixed) in that it just gets a list of database names and replaces any '?' character in the given command string by each name in turn and then executes the resulting command. It's undocumented, so in theory could change after an upgrade, but so far has remained the same up to SS2008.
Derek
March 4, 2009 at 5:04 am
Just curious to know...
how many objects there in ur DB...?
how much time it taken to execute this query..
March 4, 2009 at 6:32 am
vinuraj (3/4/2009)
Just curious to know...how many objects there in ur DB...?
how much time it taken to execute this query..
Assuming you mean Instance rather than DB, I rancreate table #c (cnt int)
exec sp_MSforeachdb 'use ?; insert #c select count(*) from sysobjects'
select count(cnt),sum(cnt) from #c
drop table #c
The result is 12792 objects across 39 databases.
The original query took 21 secs.
Derek
March 4, 2009 at 9:35 am
It's better to use sys.sql_modules instead of syscomments, since, with the latter, there's the danger that the text you are searching for will be split between rows if your object definition exceeds 4000 characters.
John
March 4, 2009 at 11:42 pm
Thanks for helping out to resolve these issue!!!
Could anyone tell me whether can we lock the column alone?
We have table as well row lock? Do we have column level lock there in sql server?
I am bit confusing how to do and i searched a lot but i couldn't able to find.
March 4, 2009 at 11:53 pm
so u were not searching NoLock from syscomments...?
thats great..... what sql server u are using...?(for column Lock..)
March 5, 2009 at 4:51 am
using both 2000 and 2005. As am a developer so i couldn't have chance to work on that. this was my doubt that's what am asking u guys.
Please clarify else tell me how to start study for these kind of DBA tasks
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply