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 advanceTry this:
create table #temp
(
name sysname
,db_size nvarchar(13)
,owner sysname
,dbid smallint
,created nvarchar(11)
,status nvarchar(600)
,compatibility_level tinyint
)
go
insert into #temp
exec sp_helpdb
select * from #temp
drop table #temp
Bill|||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...
-Regards|||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|||Thanks,
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.
-Regards
No comments:
Post a Comment