Generate documentation of tables and columns?
Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.

November 4th, 2005 9:38pm
yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal
Free Windows Admin Tool Kit Click here and download it now
November 4th, 2005 10:36pm
Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column.  These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information.  There are also 3rd party tools that can be used to document database objects as well.

Regards,
November 5th, 2005 1:43am
You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database.  www.elsasoft.org for more info.
Free Windows Admin Tool Kit Click here and download it now
August 23rd, 2006 10:33pm

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

---------------------------------------------------------------

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

---------------------------------------------------------------

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.
August 28th, 2006 6:34pm

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

Free Windows Admin Tool Kit Click here and download it now
August 28th, 2006 6:37pm

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

August 28th, 2006 8:37pm
I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.
Free Windows Admin Tool Kit Click here and download it now
August 29th, 2006 3:58pm
nice it is working fine
September 1st, 2006 3:34pm
Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;
---------------------------------------------------------------
Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM
---------------------------------------------------------------
Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()



Free Windows Admin Tool Kit Click here and download it now
September 7th, 2006 7:21am
I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?


September 14th, 2006 10:56pm

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks
Free Windows Admin Tool Kit Click here and download it now
September 18th, 2006 8:51pm
Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!
September 22nd, 2006 1:48am
May i know the string which you are calling from command line
Free Windows Admin Tool Kit Click here and download it now
September 26th, 2006 1:52pm
You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.
March 27th, 2007 1:28pm
You can generate data dictionary with Dataedo. It reads schema and table/columns descriptions (extended properties). It reads foreign keys, views, procedures and functions too.
Free Windows Admin Tool Kit Click here and download it now
April 16th, 2015 7:11pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics