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
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