Post reply

Passing a variable to a system function

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

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

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

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply