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

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.

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

sgfgdffgdfd-jjjjj-hhhhh

becomes

[sgfgdffgdfd-jjjjj-hhhhh]

Dave

|||

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

|||

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

|||

same problem man. did u try it?

thank thee

|||

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

|||

amazing what did u change amigo how many "?"

u re d man

|||never mind amigo thanks a lot