Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Documentation Expand / Collapse
Author
Message
Posted Tuesday, March 1, 2011 5:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
Hi,
I am planning to add the database details into a word document.
I have created tables to store basic information from system tables.
I want to write the details into word file.

Example.
Table Name Table_Description Column Column_descript
MyTable This is my table Col1 this is col1
Col2 this is col2
Col3 this is col3

Table Name Table_Description Column Column_descript
MyTable_1 This is my table2 Col1 this is col2
Col2 this is col2
Col3 this is col2

I want to put all the above data into word document.
Is it possible to write this data via sql server 2000?

Thanks for the help..
Post #1071124
Posted Tuesday, March 1, 2011 5:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
actually, what a lot of people do is to put the data in the database itself by using extended properties...(sys.sp_addextendedproperty / fn_listextendedproperty)

you can put descriptions up to 255 chars in length i think about the database, separate descriptions about objects like proc/views/tables, and separate descriptions about each column...
then you read those descriptions back and put them in the presentation document of your choice(word...excel...html...whatever)

here's a simple example about a single stored procedure; play with it and see how the same concept can be used for every database object.
CREATE procedure [dbo].[sp_find]         
@findcolumn varchar(50)
as
begin
set nocount on
select sysobjects.name as TableFound,syscolumns.name as ColumnFound
from sysobjects
inner join syscolumns on sysobjects.id=syscolumns.id
where sysobjects.xtype='U'
and (syscolumns.name like '%' + @findcolumn +'%'
or sysobjects.name like '%' + @findcolumn +'%' )
order by TableFound,ColumnFound
end
GO
EXEC sys.sp_addextendedproperty
@name = N'Version',
@value = N'9.0.154.90',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'sp_find';
EXEC sys.sp_addextendedproperty
@name = N'Purpose',
@value = N'simple tool to find column or table names that are LIKE the inputed value',
@level0type = N'SCHEMA', @level0name = 'dbo',
@level1type = N'PROCEDURE', @level1name = 'sp_find';
--show all extended properties
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);
--get just the "Version" that i created:
SELECT objtype, objname, name, value
FROM fn_listextendedproperty ('Version', 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1071131
Posted Tuesday, March 1, 2011 5:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
additionally, both Enterprise Manager and SSMS GUIS's have the built in ability to add an extended property for ease of use: just go to teh table and select "Design", one of the values is teh "Description"



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1071132
Posted Tuesday, March 1, 2011 5:58 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
Thank you for the reply,
But I think I am not being able to make clear what I actually want.

In simple terms I want to document the database into word file. I work in sql server 2000.

I want to write a procedure or generate a script which I execute it and then a word file gets created with all the tables and its column explanation.

Thanks,
Harsha
Post #1071143
Posted Tuesday, March 1, 2011 6:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
yes, i understood that...
what i was telling you is compared to what i would call a "normal" process, you are doing it backwards...you put the data in the database, and then read the data into word or whatever

Word documents are not a standard format, so if you do anything fancy like adding tables to structure organize, bold fonts,a dd headers, etc, it's no longer easy for a machine to read.
because of those formatting issues, you would have to make an SSIS package to try to read your word document, parse it, and then create the commands i mentioned previously.
I'd avoid word altogether and use plain text so you could import it via BULK INSERT if you HAVE to do it from doc to the database.

I was just trying to offer information, and maybe a better strategy, based on my experience.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1071146
Posted Tuesday, March 1, 2011 6:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 12,877, Visits: 31,791
harsha.bhagat1 (3/1/2011)
Thank you for the reply,
But I think I am not being able to make clear what I actually want.

In simple terms I want to document the database into word file. I work in sql server 2000.

I want to write a procedure or generate a script which I execute it and then a word file gets created with all the tables and its column explanation.

Thanks,
Harsha


or maybe you wanted to just generate the lsit of tables/columns like this?:
select objz.name as TableName,
colz.name as ColumnName,
colz.colid
from sysobjects objz
inner join syscolumns colz
on objz.id = colz.id
WHERE objz.xtype='U'
ORDER BY objz.name,colz.colid



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1071150
Posted Tuesday, March 1, 2011 6:25 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
I have created a procedure as proc_Admin_table_ColumnDescription.
When I exeute this procedure it returns me with the table and column description in result pane, which is fine. But I want this result to in MS Word but not like a plain list but in a tabular form for each table.
Post #1071170
Posted Tuesday, March 1, 2011 7:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:39 PM
Points: 446, Visits: 653
Is there a any way that I can put the data into MS Word via sql statements??

Searching on net, finding sometihng called sp_oacreate, method..
Any idea about this ..
Post #1071210
Posted Monday, May 2, 2011 11:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 3, 2011 11:25 AM
Points: 51, Visits: 175
Here is one more Free Tool to document Sql Server
Post #1101807
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse