Dynamic SQL: querying sysobjects

[LFCfan]

Hi everyone.

I’m trying to create a stored proc which, given a database name, will look in the sysobjects table for that DB and bring back the names of all stored procs beginning with up_

Effectively, I want the following query executed:

select name from MyDB..sysobjects
where name like 'up_%'
and type = 'P'

Here’s my stored proc:

create proc dbo.up_GetStoredProcs
@DBName varchar(50)
as
declare @strSQL nvarchar(1000)
set @strSQL =
' select name from ' + @DBName + '..sysobjects
where name like ''up_%''
and type = ''P'''

exec(@strSQL)
GO

However exec dbo.up_StoredProcs @DBName = ‘MyDB’ returns an empty result set, whereas if I execute the sql string directly in Query Analyser, it works fine (so the dynamic t-sql is correct), and it returns a list of names of stored procs, as expected.

I’ve also tried the following:

Set @strSQL =
' use ' + @DBName +
' select name from sysobjects
where name like ''up_%''
and type = ''P'''

but still no joy.

Any ideas? Or alternatives?
TIA
lfcfan

 

[LFCfan]

Just ignore me, the DB name was incorrect, it works fine!!

 

[grahams]

Well, if you want to do it this way, try changing your dynamic sql so that it uses a three-part DB name thus:

'select name from ' + @DBName + '..sysobjects where name like "up%" and type = "p"'

HTH,
Graham

 

[JamesLean]

This works OK for me. Try PRINTing @strSQL before executing it within your SP and make sure the SQL string is getting built correctly?:

...
set @strSQL = 'select name from ' + @DBName + '..sysobjects where name like ''up_%'' and type = ''P'''

PRINT @strSQL
exec(@strSQL)
...

--James

 

[LFCfan]

thanks Graham,
sorry you had to waste your time when it was purely my stupidity at fault!

The code in the CREATE PROC statement works ok.

 

[LFCfan]

...and that goes for you too James!

Thanks folks!
M

 

[grahams]

You're welcome. It wasn't a waste of time - and besides, in the absence of actual work from my employers, at least this place keeps my brain working

 

[sunila7]

Hi,

have a look at this thread....thread183-476438....

declare @DBName varchar(50)
declare @sql varchar(1000)
set @dbname ='Northwind'
set @Sql= 'Select spname=name
From '+ @dbname + '.dbo.sysobjects
Where type=''P'' '
print @Sql

exec sp_msforeachdb @Sql

This shld also give u the results....

Sunil