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.
my script not working when the name of a DB has - (the dash character)
akilhoffer26386
You might want to try enclosing the database name in square bracked so that
becomes
HPD
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
coolcoder
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.
silentC
MigiTheGuru
amazing what did u change amigo how many " "
u re d man
IamHuM
I got the same error you did with your original query; this version eliminates the execution errors.
cgn
same problem man. did u try it
thank thee