Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004

    Exclamation Unanswered: How to do a select from sp_helpdb's output ?

    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

  2. #2
    Join Date
    Sep 2005

    select output from helpdb

    Try this:

    create table #temp
    name sysname
    ,db_size nvarchar(13)
    ,owner sysname
    ,dbid smallint
    ,created nvarchar(11)
    ,status nvarchar(600)
    ,compatibility_level tinyint
    insert into #temp
    exec sp_helpdb

    select * from #temp

    drop table #temp


  3. #3
    Join Date
    Mar 2004
    Thanks for your solution,
    But think about a highly busy environment with many concurent users.Should I create a temp table for each session? I can append the session_id or something like that to achieve unique temp table names but is this the best way in your opinion?
    I am not satisfied,maybe there is a better solution...maybe...

  4. #4
    Join Date
    Jan 2003
    Nottinghamshire, UK
    You don't need to worry about SessionId's for the Temp table because the table will only be visible to the connection/user who created it.

    Good Tip Cascred with the

    insert into #temp
    exec sp_helpdb

    I've wondered myself how to capture these outputs before and you've just given me the answer.

    I know MSoft don't recommend selecting directly from sysfiles and i'm not sure why - probably because they reserve the right to change it's structure in future releases which may mean that any code may stop working.

    Having said that I would think that theres a great deal more chance that the SP_helpDB output is more likely to change than the filename column in sysobjects.

    Personnaly I'd go with the select filename from sysobjects option and move on
    but if you still don't like that maybe another option would be to capture the output of XP_CmdShell something like
    xp_cmdshell 'dir c:\Program Files\Microsoft SQL Server\Mssql\Data\*.*'

    Good Luck
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Mar 2004
    you don't know the path of SQL Server in each system,so you can't use xp_cmdshell always: You can set an environment variable in each system to point to SQL Server path.Even in this case maybe you have created the datafile in othe place and the scenario goes on...
    Second: I don't want all of those files,I need the filename for a specific database name,and these are not equal or even similar always.
    I am agree with you about changing in output of sp_helpdb versus structure of sysfiles.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts