R.Tutus


Hi, this script uses MSforEachDB to check all the SPs in all the databases and look for a keyword LockCookie.

The script runs well except when there is a database with a dash - in ots name.
ie: When
in the script is replaced by a database whose name contains a dash -
the dash and the rest of the database name after the dash is ignored.

And I get the message for example:
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_f5c0f71f'. No entry found with that name. Make sure that the name is entered correctly.

Here is the script:
exec sp_MSforeachDB
'
use
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, '' '' as dbName
from .sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'


You can run it and if u have a DB named: sgfgdffgdfd-jjjjj-hhhhh for example you will see the error
How can I fix my script to consider databases with - as well in sys.objects

Thanks a lot for your help.





Re: my script not working when the name of a DB has - (the dash character)

Waldrop


You might want to try enclosing the database name in square bracked so that

sgfgdffgdfd-jjjjj-hhhhh

becomes

[sgfgdffgdfd-jjjjj-hhhhh]


Dave






Re: my script not working when the name of a DB has - (the dash character)

R.Tutus

yeah man. but what do i do in my script

the script is dynamic:

try jus this little script and it gives the error:

exec sp_MSforeachDB
'
use
select o.name,
'' '' as dbName
from .sys.objects as o
where o.type=''P'''

Thanks a lot







Re: my script not working when the name of a DB has - (the dash character)

Waldrop

I changed it to:

exec sp_MSforeachDB
'
use
select o.name,
'' '' as dbName
from [ ].dbo.sysobjects as o
where o.type=''P'''

and it works fine against all of my databases -- even against my [test-hyphen] database.


Dave





Re: my script not working when the name of a DB has - (the dash character)

R.Tutus

same problem man. did u try it

thank thee






Re: my script not working when the name of a DB has - (the dash character)

Waldrop

I got the same error you did with your original query; this version eliminates the execution errors.

exec sp_MSforeachDB
'
use [ ]
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, '' '' as dbName
from [ ].sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'

Dave





Re: my script not working when the name of a DB has - (the dash character)

R.Tutus

amazing what did u change amigo how many " "

u re d man






Re: my script not working when the name of a DB has - (the dash character)

R.Tutus

never mind amigo thanks a lot