SQL Server Documentor

Question

Post reply

SQL Server Documentor

Rudy Panigas

SSChampion

Points: 10702
More actions
October 16, 2012 at 3:38 pm

#265784

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

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

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

Lee Linares SSCrazy Points: 2727 More actions · Answer 1

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: 10702 More actions · Answer 2

Hello Lee,

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

Glad you like it 🙂

Rudy

wdolby Old Hand Points: 376 More actions · Answer 3

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: 10702 More actions · Answer 4

Thanks again to everyone for the improvements 🙂

Rudy

jasona.work SSC Guru Points: 50118 More actions · Answer 5

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: 10702 More actions · Answer 6

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 More actions · Answer 7

chvreddy78

SSC Enthusiast

Points: 122

December 25, 2012 at 10:22 pm

#1571490

hi

very good script

Ken SSCoach Points: 17191 More actions · Answer 8

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

david.shink SSC Enthusiast Points: 181 More actions · Answer 9

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: 10702 More actions · Answer 10

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 11

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: 10702 More actions · Answer 12

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: 2874 More actions · Answer 13

Have the earlier comments been incorporated into this script?

Rudy Panigas SSChampion Points: 10702 More actions · Answer 14

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