September 25, 2005 at 4:58 am
Hi friends,
I want to select only filename column of sp_helpdb's output.But I don't know how should I do this? Other way is doing a direct select from sysfiles or sysdatabases but as you know microsoft doesn't recommand this way. Also I can select that filed in my application level but it is strange for me: Of course sql server should has a proper way to do a select from output curosr of this procedure,Doesn't it?
-Thanks in advance
September 25, 2005 at 6:01 am
Can't find a way of doing this without using sysfiles - here's an example, if you need it:
declare @dbname varchar(100)
declare @strSQL nvarchar(500)
set @dbname = 'master'
set @strSQL = 'select name, fileid, filename from ' + @dbname + '.dbo.sysfiles'
exec sp_executesql @strSQL
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 25, 2005 at 6:18 am
Lets think in reverse form! I have a procedure that returns a heavy cursor(not just like st_helpdb).Sould I transfer such a set to client (This can be application server or really a client application)? I think this is so bad way! So I infer microsoft guys should have innovated another way! Maybe I am so optimistic! Clarify me.
-Thanks
September 26, 2005 at 7:16 am
What is it you need to do exactly??
September 26, 2005 at 8:01 am
The simplest solution to this problem, and not neccessarily the most elegant is to copy the stored procedure sp_helpdb into a new stored procedure called something like sp_helpdbname. Once you have changed the name of the stored procdure at the top, you can then move to the bottom of the code and rem out from the select statement what you don't want returned.
This also allows you to see how they built the procedure and if you want remove any uneccessary code from your new stored procedure.
September 26, 2005 at 10:23 am
Try this, creates a temporary table (#tmp), populates it with the output from 'sp_helpdb' using EXEC. You can then select from the temp table as normal :
create table #tmp (name varchar(200),
varchar(20),
owner varchar(50),
dbid int,
created datetime,
status varchar(4000),
compat int)
go
insert #tmp exec sp_helpdb
select * from #tmp where name like 'm%' -- Get all databases beginning with 'm'
HTH
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply