Best practice for data dictionary in SQL Server 2005

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

Hi, all experts,

Any advices?

Thanks a lot.

With best regards,

Yours sincerely,

|||I think the word data dictionary is not quite sure for us, do you mean data dictionary like in Oracle where you can grap the information of the objects stored in the database ?

HTH, Jens Suessmeyer

http://www.sqlserver2005.de
|||

Hi,Jens,

Thanks. Yes, what I mean here data dictionary is the description of the database objects from tables to all attributes in tables. I mean when other users other than the database designers are using the database data, without any supportive data dictionary for explanation about what the database and all its fields are about , users definitely have difficulty understanding it to further analyze it.

Hope my information above is clear for my question to be understood.

Thank you very much.

With best regards,

Yours sincerely,

|||The simple collection of the database objects can be found in the INFORMATION_SCHEMA Views. If you want to implement custom description, you can use the extended properties for that. If you have the chance to use Visio are another other data modeling program, I would choose that ín your case.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi, Jens,

Thank you very much for your kind and very helpful guidance and advices.

But the INFORMATION-SCHEMA views are generated automatically by the system? not customized by the database designer, the INFORMATION-SCHEMA VIEWS are just the collection of the data types, scale etc. while not user-defined description of each attibutes like what is this attribute used for, what dose the data in this attribute represent etc.

Is it possible for users to alter the view like INFORMATION_SCHEMA.COLUMNS to add an addtional column used to describe the columns used there by using T-SQL query?

Yours further advices and guidances are most appreciated.

With best regards,

Yours sincerely

|||

You can use sys.sp_addextendedproperty to add column description. You could find more detail at BOL, ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565483ea-875b-4133-b327-d0006d2d7b4c.htm.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a table description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a column description'
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo'
,@.level1type=N'TABLE'
,@.level1name=N'TableName'
,@.level2type=N'COLUMN'
,@.level2name=N'Col1'
GO

|||

Hi, Somjai,

Thank you very much for your kind advices.

But I copied the above scripts into the query window, it did not work? Do I need to replay any of the amove information with my specific information? Thanks a lot for your further guidance and advices.

With best regards,

Yours sincerely,

|||

Helen,

Yes, you need to replace with your specific information.

EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template table' --Your table definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
GO
EXEC sys.sp_addextendedproperty
@.name=N'Caption'
,@.value=N'This is a template column' --Your column definition
,@.level0type=N'SCHEMA'
,@.level0name=N'dbo' --Your scheme name (when it is not dbo)
,@.level1type=N'TABLE'
,@.level1name=N'TableName' --Your table name
,@.level2type=N'COLUMN'
,@.level2name=N'Col1' --Your column name
GO

You will need to repeat second EXEC for all of your columns in this table. I usually create a file for each tables (use table name as file name) and put these statements right after my table creation in the same file. I also put these files in VSS which allow me to version and share with other people.

Regards,

|||

Hi,Somjai, thank you very much for your kind guidance and advices.

But I cant see any advantage by using this system stored procedure to add the descriptions for the columns by repeating so many times for all the columns which jsut result in a caption added to the extened property for the objects.

The above results seems not as easy as directly adding the description for the objects within the object's property dialog.

By the way, what is VSS as you mentioned?

Thanks a lot.

With best regards,

Yours sincerely,