Documenting tables/fields - utility?

As part of a project documentation I want to list out the fields of all the tables to say Excel to plug in a description of the field and other notes.

Is there a tool in EM that would facilitate? Or, is there a utility that would work or a SQL script? Any recommendations is greatly appreciated.

Thanks,

PeterI'll never understand what motivates people to want to take DATA from a DATABASE and store it in a SPREADSHEET.
Tables and columns are already enumerated within the database, and a description of each column can be entered through Enterprise Manager.
You can query the system tables to list all the tables with their columns and descriptions and (if you absolutely insist...) export the results to an Excel file.|||Erwin is good for this. Which reminds me I am about a month and a half behind on that.|||Hey - for a blind guy, you sure help me see the light.

Thanks blindman.|||I keep a bottle of hand-sanitizer with me for occasions when a client requires me to open up Excel, but otherwise all the data I deal with stays in databases.|||LOL - I like that line about having to use hand sanitizer...I will be using that one myself.

I did find a quick query syntax at:

http://searchvb.techtarget.com/tip/1,289483,sid8_gci876303,00.html?bucket=ETA

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

I altered the above to bring in the description that a user can enter against each field in the tables:

SELECT
table_name=sysobjects.name,
column_name=syscolumns.name,
datatype=systypes.name,
length=syscolumns.length,
descr=sysproperties.value
FROM sysobjects inner JOIN syscolumns ON sysobjects.id = syscolumns.id
inner join systypes on syscolumns.xtype=systypes.xusertype
left join sysproperties on sysproperties.id=syscolumns.id and syscolumns.colid=sysproperties.smallid
where sysobjects.xtype='U'
order by sysobjects.name,syscolumns.colid

This is fine for my immediate needs.|||If you liked that one, you'll love this one ... and it's guarenteed not to become obsolete with sql server upgrades ...


select * from information_schema.columns|||Tom -

Thanks - another good one for quick data generation.

Documenting SQL Jobs

Does anyone know any tools or scripts to use for
documenting SQL Jobs. I need to take the results and
create an excel or word file for management. I would need
it to list the job name, schedule, frequency...so on.
Trying to script this is not easy in that the sysjobs and
sysjobschedules tables use numbers to represent everything
about the schedule and all.
Any help is appreciated.
Thanks,
Van JonesHow about a simple SQL Query, and put the output in Excel? The tables you
need to read from aren't that difficult to work with. Only mess is the
dateformat and frequency stuff, but there is some good stuff at
www.SQLDev.Net that help you with this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
> Does anyone know any tools or scripts to use for
> documenting SQL Jobs. I need to take the results and
> create an excel or word file for management. I would need
> it to list the job name, schedule, frequency...so on.
> Trying to script this is not easy in that the sysjobs and
> sysjobschedules tables use numbers to represent everything
> about the schedule and all.
> Any help is appreciated.
> Thanks,
> Van Jones|||Hey thanks for the reply Tibor. I've been down that road
before a couple years ago and didn't have a great deal of
luck. I got it to work, but it wasn't very clean. I was
just hoping for a tool to do it for me. However, I saw a
reply from Sue under dbforums to the same question that
had the following article listed:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15560
It has a sp called usp_Scheduled_Jobs that will do it all
for you....extremely nice and useful.
Thanks,
Van Jones
MCDBA, MCSE, MCSA, MCAD
>--Original Message--
>How about a simple SQL Query, and put the output in
Excel? The tables you
>need to read from aren't that difficult to work with.
Only mess is the
>dateformat and frequency stuff, but there is some good
stuff at
>www.SQLDev.Net that help you with this.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
>> Does anyone know any tools or scripts to use for
>> documenting SQL Jobs. I need to take the results and
>> create an excel or word file for management. I would
need
>> it to list the job name, schedule, frequency...so on.
>> Trying to script this is not easy in that the sysjobs
and
>> sysjobschedules tables use numbers to represent
everything
>> about the schedule and all.
>> Any help is appreciated.
>> Thanks,
>> Van Jones
>
>.
>|||OK, I just wanted to point the option of using these tables, in case you
haven't used them already. Yes, some things aren't that easy to deal with
(mostly the freq- things, IMO). Hopefully Sue's proc will help you :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0ded01c3a48e$787439a0$a601280a@.phx.gbl...
> Hey thanks for the reply Tibor. I've been down that road
> before a couple years ago and didn't have a great deal of
> luck. I got it to work, but it wasn't very clean. I was
> just hoping for a tool to do it for me. However, I saw a
> reply from Sue under dbforums to the same question that
> had the following article listed:
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15560
> It has a sp called usp_Scheduled_Jobs that will do it all
> for you....extremely nice and useful.
> Thanks,
> Van Jones
> MCDBA, MCSE, MCSA, MCAD
> >--Original Message--
> >How about a simple SQL Query, and put the output in
> Excel? The tables you
> >need to read from aren't that difficult to work with.
> Only mess is the
> >dateformat and frequency stuff, but there is some good
> stuff at
> >www.SQLDev.Net that help you with this.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Van Jones" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
> >> Does anyone know any tools or scripts to use for
> >> documenting SQL Jobs. I need to take the results and
> >> create an excel or word file for management. I would
> need
> >> it to list the job name, schedule, frequency...so on.
> >> Trying to script this is not easy in that the sysjobs
> and
> >> sysjobschedules tables use numbers to represent
> everything
> >> about the schedule and all.
> >>
> >> Any help is appreciated.
> >>
> >> Thanks,
> >>
> >> Van Jones
> >
> >
> >.
> >|||It's a reference to an article in SQL mag a couple of years
or so ago that has a pretty nice script to grab the job,
schedule, avg run time, max run time, some other properties.
It's pretty good - I've used it quite a bit with just a
couple modifications.
-Sue
On Thu, 6 Nov 2003 18:58:09 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote:
>OK, I just wanted to point the option of using these tables, in case you
>haven't used them already. Yes, some things aren't that easy to deal with
>(mostly the freq- things, IMO). Hopefully Sue's proc will help you :-)

Document Map for Excel

I've created a document map which displays a text string as the bookmark label (Document map label) when exporting to PDF (e.g. "January 2004", "March 2004"). However, when exporting to Excel, the tabs are named "Sheet1", "Sheet2", etc.

Is it possible to configure a report such that the tab names when exported to excel match the labels used in PDF?Excel worksheets (tabs) are a completely different concept than PDF bookmarks.
Excel worksheets are created based on the pages in the report. Support for providing specific names for Excel worksheets (i.e. report sections) is under consideration for a future release.
-- Robert