Where should I document my tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Where should I document my tables?

Author

Topic

lovray
Starting Member

9 Posts

Posted - 2009-08-04 : 17:25:51

I am using microsoft SQL server management studio 2005.

I am creating a bunch of new tables for a project and I was just wondering if there was some standard as to where I can put documentation regarding my tables( ie,why the table exists, what scripts use this table, what particular columns are used for, etc)?

Thanks. - Ray

lovray
Starting Member

9 Posts

Posted - 2009-08-05 : 13:07:44

Sorry, I may have been unclear. Does management studio provide an area for documentation somewhere or should I simply put the documentation in comments above the sql "Create Table xxxx" code? Thanks. any feed back would be great -Ray

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-05 : 13:16:46

You can use extended properties to store the definition in the "catalog"

By I prefer to keep my own data dictionary tables


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RHO_Columns]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RHO_Columns](
	[TABLE_CATALOG] [nvarchar](128) NULL,
	[TABLE_SCHEMA] [sysname] NULL,
	[TABLE_NAME] [sysname] NOT NULL,
	[COLUMN_NAME] [sysname] NULL,
	[IONS_Id] [char](5) NULL,
	[Comments] [varchar](max) NULL,
	[Add_by] [varchar](7) NOT NULL,
	[Upd_by] [varchar](7) NOT NULL,
	[Add_Dt] [datetime] NOT NULL,
	[Upd_Dt] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RHO_Tables]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RHO_Tables](
	[TABLE_CATALOG] [nvarchar](128) NULL,
	[TABLE_SCHEMA] [sysname] NULL,
	[TABLE_NAME] [sysname] NOT NULL,
	[Comments] [varchar](max) NULL,
	[Add_by] [varchar](7) NOT NULL,
	[Upd_by] [varchar](7) NOT NULL,
	[Add_Dt] [datetime] NOT NULL,
	[Upd_Dt] [datetime] NOT NULL
) ON [PRIMARY]
END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

lovray
Starting Member

9 Posts

Posted - 2009-08-10 : 16:50:17

Thanks. That's a good Idea.. create a table that holds all documentation on your tables. I'm surprised that I haven't got more responses to this subject given the number of reads. It seems that a lot of people don't care to document their databases. Cheers -Ray

X002548
Not Just a Number

15586 Posts

Posted - 2009-08-10 : 16:56:37

That may very well be true....

I can't think of the last db that I wasn't involved in creation that had a data dictionary

Hell, I'm glad when they get to 3rd normal form

Good Luck

EDIT: There's an added benefit of having context info available if there are needs for help for the user for what a column means

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources