SQL Server Documentor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rudy Panigas » SQL Server Documentor

SQL Server Documentor

Rate Topic

Display Mode

Topic Options

Author

Message

Rudy Panigas

Posted Tuesday, October 16, 2012 3:38 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 306, Visits: 1,015

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

Post #1373554

Lee Linares

Posted Monday, November 19, 2012 11:20 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 12:40 PM
Points: 70, Visits: 588

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

Post #1386514

Rudy Panigas

Posted Monday, November 19, 2012 12:43 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 306, Visits: 1,015

Hello Lee,

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

Glad you like it :)

Rudy

Post #1386544

wdolby

Posted Tuesday, November 20, 2012 12:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:20 AM
Points: 151, Visits: 272

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

Post #1387106

Rudy Panigas

Posted Tuesday, November 27, 2012 12:48 PM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 306, Visits: 1,015

Thanks again to everyone for the improvements Smile

Rudy

Post #1389383

jasona.work

Posted Tuesday, December 11, 2012 2:53 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:49 PM
Points: 469, Visits: 2,361

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

Post #1395336

Rudy Panigas

Posted Wednesday, December 12, 2012 6:48 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 306, Visits: 1,015

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 Smile

Post #1395644

chvreddy78

Posted Tuesday, December 25, 2012 10:22 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:34 PM
Points: 26, Visits: 320

hi
very good script

Post #1400143

KWymore

Posted Wednesday, January 02, 2013 6:43 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:57 PM
Points: 2,575, Visits: 1,533

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

Post #1402141

« Prev Topic | Next Topic »

Permissions