Find which SPs have quoted_identifier set wrong

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

(No huge rush, the someone-else has already fixed the recent case)

SQL 2K, SP3Ed Murphy wrote:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

You can query against the SP text as follows.

select o.[name] as 'ProcName', c.[text] as 'ProcText'
from sysobjects o
join syscomments c on o.[id] = c.[id]
where o.[name] like 'usp%'

This is based on the assumption that all your SPs start with 'usp'.
Applying a WHERE clause should enable you to filter out certain procs.

Somebody please step in and correct this as I have a feeling it's not
the best way.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Our client's application software requires all stored procedures to
have quoted_identifier set a certain way. I've tripped over this a
few times and promptly fixed it, but this morning, I had to
troubleshoot a case where someone else tripped over it. In such a
situation, how can I identify which SP(s) have it set the wrong way?

SELECT name
FROM sysobjects
WHERE objectproperty(id, 'ExecIsQuotedIdentOn') = 0

I'm assuming here that OFF is the incorrect position.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Find in SPs option for SQL

Hi All,
Just a quick question, is there any option to search in all stored
procedures in a database for a particular string, without scripting out
the whole lot?
Thanks
WillHow about a query against syscomments (2000) or sys.sql_modules (2005), for
example:
USe pubs
SELECT OBJECT_NAME(id) FROM syscomments WHERE text LIKE '%authors%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1146735219.374369.41630@.g10g2000cwb.googlegroups.com...
> Hi All,
> Just a quick question, is there any option to search in all stored
> procedures in a database for a particular string, without scripting out
> the whole lot?
> Thanks
> Will
>|||Thanks Tibor,
I was hoping that there was some nice GUI addition to Management
studio, but I suppose it's going to have to be a SQL query
Will

find all sps where a field is used

In my database i want list of all stored procedures where i have used a
specific field.
Is there any way to do this.. any system sp which does thisYou have to search the prcedure definition for that, because AFAIK
there is no system binding or something like this where the information
is stored which columns are used. So the approach could be SELECT
Routine_name from INFORMATION_SCHEMA.Routines Where Routine_definition
like '%SomeValue%'
HTH, jens Suessmeyer.|||The information isn't stored in any system table, except for the source code
for the procedure
(syscomments). So you can use a LIKE predicate against the text column in sy
scomments, but be
careful as syscomments can break the line (use several rows for a procedure)
in the middle of a word
(at least it used to, perhaps newer versions are better).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vikram" <aa@.aa> wrote in message news:OmDLgniAGHA.204@.TK2MSFTNGP15.phx.gbl...d">
> In my database i want list of all stored procedures where i have used a
> specific field.
> Is there any way to do this.. any system sp which does this
>|||This might not be very pretty, but it should do the job:
CREATE TABLE #Depends
([Name] nvarchar(128),[type]nvarchar(128))
INSERT INTO #Depends
EXEC SP_Depends 'Table1'
SELECT [Name] from #depends
WHERE [type] = 'stored procedure'
AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
LIKE '%FieldName%')
Drop Table #depends
Markus|||(The following applies to SQL 2000. I haven't tried it in SQL 2005.)
I'm assuming that enterprise manager uses sp_depends to display dependancy
information. If this is true, your suggestion will only work if objects are
created in the correct sequence.
For example, it works if:
1. Create Table1.
2. Create proc MyProc1 that select Data from Table1.
If you drop table1 and recreate it, dependancy information is lost and will
not be shown. Same holds true for stored procedures. If you create a store
d
procedure that references another stored procedure that doesn't exist yet,
you will not have dependancy information even after you create the other
stored procedure.
IMHO, the only sure fired way is to script the database to a text file and
search it for the column name and/or table names that you are interested in.
Also, if any view, stored procedure, or user-defined function uses an *,
then you can't search by column name.
Hope that helps,
Joe
"m.bohse@.quest-consultants.com" wrote:

> This might not be very pretty, but it should do the job:
> CREATE TABLE #Depends
> ([Name] nvarchar(128),[type]nvarchar(128))
> INSERT INTO #Depends
> EXEC SP_Depends 'Table1'
> SELECT [Name] from #depends
> WHERE [type] = 'stored procedure'
> AND OBJECT_ID([Name]) IN (Select [id] from syscomments where [text]
> LIKE '%FieldName%')
> Drop Table #depends
> Markus
>