SQL Server Documentor

  • Rudy Panigas

    SSChampion

    Points: 10695

    Comments posted to this topic are about the item SQL Server Documentor

    Rudy

  • Lee Linares

    SSCrazy

    Points: 2662

    Rudy,

    Thanks for sharing this excellent script. I ran into one small issue and got this error:

    Msg 15281, Level 16, State 1, Procedure sysmail_help_status_sp, Line 0

    SQL Server blocked access to procedure 'dbo.sysmail_help_status_sp' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

    This was because Database Mail was not enabled.

    I corrected this by adding a simple IF statement around the INSERT statement. So it went from this:

    CREATE TABLE #Database_Mail_Details

    (Status NVARCHAR(7))

    INSERT INTO #Database_Mail_Details (Status)

    Exec msdb.dbo.sysmail_help_status_sp

    To this:

    CREATE TABLE #Database_Mail_Details

    (Status NVARCHAR(7))

    IF EXISTS(SELECT * FROM master.sys.configurations WHERE configuration_id = 16386 AND value_in_use =1)

    BEGIN

    INSERT INTO #Database_Mail_Details (Status)

    Exec msdb.dbo.sysmail_help_status_sp

    END

    You also had the the outputs for Section 7 (Last Backup Dates) and section 8 (List of SQL Jobs) reversed.

    Thanks again for taking the time to share this script.

    Lee

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello Lee,

    Thanks for the update. Sorry about that, it not always easy to get real testing for scripts.

    Glad you like it 🙂

    Rudy

    Rudy

  • wdolby

    Old Hand

    Points: 365

    Great script. I noticed not all of my databases were showing under --> Database(s) Details <--. The join is using name and several of my databases have logical names that don't match the actual database name.

    I changed the join condition to use database_id and filtered out the extra rows by looking only for the primary file.

    --> Database(s) Details <--

    SELECT...

    INTO #Databases_Details

    FROM SYS.DATABASES D

    INNER JOIN sys.master_files S

    ON D.database_id= S.database_id

    WHERE s.file_id = 1

  • Rudy Panigas

    SSChampion

    Points: 10695

    Thanks again to everyone for the improvements 🙂

    Rudy

    Rudy

  • jasona.work

    SSC-Forever

    Points: 49870

    Just gave this a try, and love it!

    Did notice one quirk though.

    Section 7) Last backup dates and section 8) List of SQL jobs are flip-flopped.

    Section 7 claims to be the last backup dates, but instead gives the list of SQL Jobs, and 8) gives the list of Last backup dates.

    Jason

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello Everyone,

    I have just updated the script to have the 7) and 8) comments reversed. Sorry about that and the delays to get it corrected. The update should be on the site soon.

    Thanks,

    Rudy 🙂

    Rudy

  • chvreddy78

    SSC Enthusiast

    Points: 122

    hi

    very good script

  • Ken Wymore

    SSCoach

    Points: 16365

    Nice script Rudy. Thanks to Lee and WDolby for their recommended fixes too.

  • david.shink

    SSC Enthusiast

    Points: 181

    I just now (7/11/13) saw your post "The SQL Doumenter". There are lots of posts that are nice, but this one looks genuinely useful. It looks like you put a lot of work into this. Thank you. -David Shink, Oracle, and lately, SQL Server DBA.

  • Rudy Panigas

    SSChampion

    Points: 10695

    Hello David,

    Glad you find the script useful. I will be updated it soon with additional features so look if an update sometime in August.

    Thanks,

    Rudy

    Rudy

  • SQLQuest29

    SSCrazy Eights

    Points: 8203

    Highly suggest to look into : SQL Server & Windows Documentation Using Windows PowerShell[/url] by Kendal Van Dyke

    It collects much more info and is flexible.

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Rudy Panigas

    SSChampion

    Points: 10695

    SQLQuest29 (1/2/2014)


    Highly suggest to look into : SQL Server & Windows Documentation Using Windows PowerShell[/url] by Kendal Van Dyke

    It collects much more info and is flexible.

    Why would I use PowerShell when the information you need can be collected with T-SQL scripting. Maybe you could create the PowerShell and post the script?

    Personally I'm not into PowerShell, feel like is a programming language for System administrators and Database administrators.

    Rudy

  • Robert Sterbal-482516

    SSCrazy

    Points: 2784

    Have the earlier comments been incorporated into this script?

  • Rudy Panigas

    SSChampion

    Points: 10695

    Robert Sterbal-482516 (9/30/2014)


    Have the earlier comments been incorporated into this script?

    Sorry about the delays. I haven't added the changes yet to the script here. However, I am creating a newer version (with the changes) so that the script executes as a stored procedure and saves the data into table. This will allow for the creation of a nice report.

    Once completed, I will update this site.

    Thanks,

    Rudy

    Rudy

Viewing 15 posts - 1 through 15 (of 25 total)

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