Passing a variable to a system function

• smthembu

  Hall of Fame

  Points: 3464

  More actions

  May 12, 2010 at 3:14 am

  #235033

  Hi All

  I need some help, I need to collect database file location by passing a variable to sp_helpdb in one go rather than running select * from sys.databases then copy db name to the above function to get database file location. How can I do that?

  Thanks

  It's better to fail while trying, rather than fail without trying!!!

• Carolyn Richardson

  SSCrazy Eights

  Points: 8357

  More actions

  May 12, 2010 at 3:20 am

  #1166041

  This might help....

  IF EXISTS (SELECT * FROM tempdb..sysobjects

  WHERE id = object_id(N'[tempdb]..[#Results]'))

  DROP TABLE #Results

  CREATE TABLE #Results (ServerName VARCHAR(100), DatabaseName VARCHAR(100), NAME VARCHAR(100), filename VARCHAR(200))

  EXEC sp_MSForEachDB

  'INSERT INTO #Results(ServerName, DatabaseName, Name, filename)

  SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS ServerName,

  ''?'' as DatabaseName,

  [?]..sysfiles.name,

  [?]..sysfiles.filename

  From [?]..sysfiles'

  --for all databases

  select * from #Results

  or

  DECLARE @DatabaseName varchar(50) = db_name()

  SELECT

  ServerName,

  DatabaseName,

  Name as FileName,

  FileName as FullPath

  FROM #Results

  WHERE DatabaseName = @DatabaseName

  ORDER BY DatabaseName, NAME

  drop table #Results

  Facts are stubborn things, but statistics are more pliable - Mark Twain
  Carolyn
  SQLServerSpecialists[/url]

• smthembu

  Hall of Fame

  Points: 3464

  More actions

  May 12, 2010 at 3:35 am

  #1166050

  Thanks a lot for your help, the first script works perfect but the second script is failling with invalid object name #result

  Thanks

  It's better to fail while trying, rather than fail without trying!!!

• Carolyn Richardson

  SSCrazy Eights

  Points: 8357

  More actions

  May 12, 2010 at 5:41 am

  #1166105

  You still have to create the #Results table for the second script to work.

  Regards

  Carolyn

  Facts are stubborn things, but statistics are more pliable - Mark Twain
  Carolyn
  SQLServerSpecialists[/url]