[b]QUOTED_IDENTIFIER ON - How to find in which stored proc.?[/b] 1

[dobrios]

Hello,
I'm wondering if there's a way to programmatically find a name of stored procedure that contains "SET QUOTED_IDENTIFIER ON" line?

I looked in sysobjects table, but there's nothing beside procedure name.
Any ideas?

Thanks much.

Steve

 

[hmckillop]

its one of the compiled advanced options of the proc. Dont have the code at home, but will post it tomorrow when in work - if you need it urgently I think its either one of the advanced options or settings ie 'isquotedidentifier'



"I'm living so far beyond my income that we may almost be said to be living apart

 

[mrdenny]

You should be able to find it in the syscomments table.

select name from sysobjects
where id in (select id from syscomments where text like '%SET%QUOTED_IDENTIFIER%ON%')

Keep in mind that this won't work perfectly. It will only work if the string SET QUOTED_IDENTIFIER ON is within the 4k block that is stored in each record. You should be ok though.

This will only work if the SET QUOTED_IDENTIFIER ON is WITHIN the stored procedure code.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[hmckillop]

this is what I had for doing the same thing you require

Declare @v_temp Table (Objectid bigint, objectname varchar(100), xtype varchar(3))
declare @v_temp_count int, @v_i int

insert into @v_temp 
select id, name, xtype from sysobjects where xtype in ('U', 'P', 'FN') 
order by xtype, name

select @v_temp_count= count(Objectid), @v_i = 0 from @v_temp


	select 
		tmp.ObjectName, 
		objectproperty (Object_ID(tmp.objectname), 'IsAnsiNullsOn') as 'isansinullsOn', 
		objectproperty (Object_ID(tmp.objectname), 'IsQuotedIdentOn') as 'isQuotedIdentifiersOn'
	FROM 
		@v_temp tmp
	where objectproperty (Object_ID(tmp.objectname), 'IsAnsiNullsOn') = 0 or objectproperty (Object_ID(tmp.objectname), 'IsQuotedIdentOn') = 0

It will return all your relevant compiled objects (functions and procs) and will return what options have been compiled with them, i.e. Quoted Identifier and Ansi Nulls

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Steve

did the query do what you require. on looking at my code i notice I only return the procedures which havent the ansinulls or quoted identifiers turned on.
If you remove the where statement it will return everything indicating which has it on and which dont.

Cheers

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dobrios]

hmckillop",
That's exactly what I needed.
Thanks a whole bunch and star for you.

Steve