Creating a documentation

Question

Post reply

Creating a documentation

SkyBVI

SSCoach

Points: 15128
More actions
September 7, 2010 at 9:57 am

#141719

Hey,
We are creating a documentation of our all databases and their tables, procedures,triggers etc.
We are making an excel file and writing all the objects name pertaining to databases for our future records. We have to manually write the names of all tables, procs in a db to the excel files along with their creation dates. Is their any other way of keeping the records of all objects in a database or just we have to write manually in excel file ( which is very lengthy and tiresome). One of our database has 10k stored procs, so to just write their names, it would take a lot of time
Any other views or suggestions to do this.
Thanks,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 13 posts - 1 through 12 (of 12 total)

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

amy26 SSCrazy Eights Points: 8926 More actions · Answer 1

If you run this query in the database that you are trying to get info from:

select object_id, name, create_date from sys.objects where type_desc = 'USER_TABLE'

You will get a list of all the user tables in that database with their object ID and creation date. The table also has some other interesting fields as well if you want to modify the query.

The type_desc field also has other types of objects you can filter on. IE:

If you change the where clause to be 'SQL_STORED_PROCEDURE' instead of 'USER_TABLE' you will get all the info for the stored procedures.

These are some other type_desc you can filter on:

DEFAULT_CONSTRAINT

INTERNAL_TABLE

PRIMARY_KEY_CONSTRAINT

SERVICE_QUEUE

SQL_SCALAR_FUNCTION

SQL_STORED_PROCEDURE

SQL_TRIGGER

SYSTEM_TABLE

UNIQUE_CONSTRAINT

USER_TABLE

VIEW

Hope that helps.

Cory E. SSCertifiable Points: 7076 More actions · Answer 2

SKYBVI (9/7/2010)
Hey,
We are creating a documentation of our all databases and their tables, procedures,triggers etc.
We are making an excel file and writing all the objects name pertaining to databases for our future records. We have to manually write the names of all tables, procs in a db to the excel files along with their creation dates. Is their any other way of keeping the records of all objects in a database or just we have to write manually in excel file ( which is very lengthy and tiresome). One of our database has 10k stored procs, so to just write their names, it would take a lot of time
Any other views or suggestions to do this.
Thanks,
Sushant

I have used excel (the data tab) to go out to various db's and get data from servers. You should be able to define it to get updated data periodically, or adhoc. I have only used it for my own info, not to document a system, but it could be used as such.

-- Cory

Lowell SSC Guru Points: 323518 More actions · Answer 3

take a look at this wonderful script contribution:

Comprehensive HTML Database Documentation(Revised)[/url]

it produces well formatted, informative html document, which you could then copy and paste into excel, or open with excel.

I've taken that script and enhanced it to my own needs, but it's an awesome doucmentation starting point.

here's a link to the results after pointing it against my master database(whihc has a lot of user objects, but that's another issue entirely)

master_db_documentation.html

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 4

@ amy

Tht script was very nice....thxs

but i couldnt get system stored procedures from that...is it possible to get tht?

Thanks,

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 5

@ cory

Ya, If we go to data

then external data sources , we can get data from sql

but that data we get is inside the tables, i mean the columns, theier values...

i just need the names of all stored procs, tables, triggers and their creation dates...

Thanks,

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 6

@ lowell

I tried to run the script in the database

but i got many errors like invalid name like sysproperties...

Do u hav any idea about tht?

Thanks.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Lowell SSC Guru Points: 323518 More actions · Answer 7

looked like the "enhanced" version has an error in it;

this is my slightly edited version of the same script; it runs without errors on both a 2005 and 2008 database:

db_documentation_enhanced.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Cory E. SSCertifiable Points: 7076 More actions · Answer 8

SKYBVI (9/7/2010)
@ cory
Ya, If we go to data
then external data sources , we can get data from sql
but that data we get is inside the tables, i mean the columns, theier values...
i just need the names of all stored procs, tables, triggers and their creation dates...
Thanks,
Regards,
Sushant

If you can write it in t-sql / query analyzer, you can do it in excel.

-- Cory

SkyBVI SSCoach Points: 15128 More actions · Answer 9

@ lowell

Ya, tht script runs successfully, but theres no output

I mean i ran against the database, what should i get as output??

i just got query successful...

Thanks,

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 10

@cory

I couldnt get you?

Where should i write the tsql?

theres no option when i go thru data, external data source,sql server

it just asks for selecting the table and then it gives the output in the excel file.

Thanks,

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Cory E. SSCertifiable Points: 7076 More actions · Answer 11

SKYBVI (9/7/2010)
@cory
I couldnt get you?
Where should i write the tsql?
theres no option when i go thru data, external data source,sql server
it just asks for selecting the table and then it gives the output in the excel file.
Thanks,
Regards,
Sushant

you can change the excel connection to do any sql server query.

Rough explanation:

Create a connection (I find it best to not point to any specific table)

Edit the connection, click properties, click definition, then change the command text to what you want. that is what I have done anyway.

-- Cory

SkyBVI SSCoach Points: 15128 More actions · Answer 12

@ cory

Got it.

Thanks a lot.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP