SQL Server Documentor

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

    Rudy

  • 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

  • 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

  • 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

  • Thanks again to everyone for the improvements 🙂

    Rudy

    Rudy

  • 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

  • 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

  • hi

    very good script

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

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

  • 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

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

  • 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

  • Have the earlier comments been incorporated into this script?

  • 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 24 total)

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