Solved

Create Table - entering a Column Descripton

Posted on 2010-11-10
7
255 Views
Last Modified: 2012-08-14
I have written a Creat Table script and am using a Default, but would like to add the Column Description to the script. I am not sure how to incorporate the setting into the script itself. Also, are there any other command that can be used in creating a script?

Thank you in advance...

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSafety_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblSafety_Log]

GO

CREATE TABLE [dbo].[tblSafety_Log] (

	[SS_Num] [int] NOT NULL Default 0 ,

	[Emp_Num] [smallint] NOT NULL Default 0,

	[Pos_Num] [smallint] NOT NULL Default 0,

	[Insp_Date] [smalldatetime] NULL,

	[Injury] [bit] Not null Default 0,

	[I_Prev] [bit] Not null Default 0,

	[I_NonPrev] [bit] Not null Default 0,

	[I_Descrip] [varchar](50) Null,

	[Accident] [bit] Not null Default 0,

	[A_Prev] [bit] Not null Default 0,

	[A_NonPrev] [bit] Not null Default 0,

	[Ins_Report] [bit] Not null Default 0,

	[Safety_Viol] [bit] Not null Default 0,

	[ASV_Descrip] [varchar](50) Null,

	[Lost_Tm] [bit] Not null Default 0

	Primary Key (SS_Num)

) ON [PRIMARY]

GO

Open in new window

0
Comment
Question by:edrz01
7 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 100 total points
ID: 34104999
To add a column description, you use the following after the table is created:

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'<description>' , @level0type=N'SCHEMA',@level0name=N'<db schema>', @level1type=N'TABLE',@level1name=N'<db table>', @level2type=N'COLUMN',@level2name=N'<db field>'
0
 
LVL 11

Accepted Solution

by:
David Kroll earned 300 total points
ID: 34105016
You can't do it in the create table query, but there is another option.

See this:

http://stackoverflow.com/questions/1985254/sql-server-can-you-add-field-descriptions-in-create-table
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 100 total points
ID: 34105070
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:edrz01
ID: 34105130
Shaun, as I am to understand that the exec line must be for every field row correct? Can you provide and example based on the information I attached?
0
 

Author Comment

by:edrz01
ID: 34105268
For all three inputs... I am understanding that I must enter the following script for 'each' fieldname:

EXEC   sp_addextendedproperty 'MS_Description', 'EmpSS6', 'user', dbo, 'table', 'tblSafety_Log', 'column', SS_Num

I added the procedure script after the Create Table GO and executed the script and it did affect the one row. This was following the example was derived from "dkrollCTN" html link.
0
 

Author Comment

by:edrz01
ID: 34105409
Ok, so I did create one line per Filename and the results are excellent. I've attaced my code for others to see how it is used and perhaps it will be helpful to them in the future.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSafety_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tblSafety_Log]

GO

CREATE TABLE [dbo].[tblSafety_Log] (

	[SS_Num] [int] NOT NULL Default 0 ,

	[Emp_Num] [smallint] NOT NULL Default 0,

	[Pos_Num] [smallint] NOT NULL Default 0,

	[Insp_Date] [smalldatetime] NULL,

	[Injury] [bit] Not null Default 0,

	[I_Prev] [bit] Not null Default 0,

	[I_NonPrev] [bit] Not null Default 0,

	[I_Descrip] [varchar](50) Null,

	[Accident] [bit] Not null Default 0,

	[A_Prev] [bit] Not null Default 0,

	[A_NonPrev] [bit] Not null Default 0,

	[Ins_Report] [bit] Not null Default 0,

	[Safety_Viol] [bit] Not null Default 0,

	[ASV_Descrip] [varchar](50) Null,

	[Lost_Tm] [bit] Not null Default 0

	Primary Key (SS_Num)

) ON [PRIMARY]

GO



-- add description extended property to the my_table_name table's 'id' column

EXEC   sp_addextendedproperty 'MS_Description', 'EmpSS Last 6', 'user', dbo, 'table', 'tblSafety_Log', 'column', SS_Num

EXEC   sp_addextendedproperty 'MS_Description', 'Employee Number', 'user', dbo, 'table', 'tblSafety_Log', 'column', Emp_Num

EXEC   sp_addextendedproperty 'MS_Description', 'Position Number', 'user', dbo, 'table', 'tblSafety_Log', 'column', Pos_Num

EXEC   sp_addextendedproperty 'MS_Description', 'Inspection Date', 'user', dbo, 'table', 'tblSafety_Log', 'column', Insp_Date

EXEC   sp_addextendedproperty 'MS_Description', 'Injury Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', Injury

EXEC   sp_addextendedproperty 'MS_Description', 'Injury Preventable Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', I_Prev

EXEC   sp_addextendedproperty 'MS_Description', 'Injury Non-Preventable', 'user', dbo, 'table', 'tblSafety_Log', 'column', I_NonPrev

EXEC   sp_addextendedproperty 'MS_Description', 'Injury Description', 'user', dbo, 'table', 'tblSafety_Log', 'column', I_Descrip

EXEC   sp_addextendedproperty 'MS_Description', 'Accident Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', Accident

EXEC   sp_addextendedproperty 'MS_Description', 'Accident Preventable Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', A_Prev

EXEC   sp_addextendedproperty 'MS_Description', 'Accident Non-Preventable Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', A_NonPrev

EXEC   sp_addextendedproperty 'MS_Description', 'Insurance Report Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', Ins_Report

EXEC   sp_addextendedproperty 'MS_Description', 'Safety Violation Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', Safety_Viol

EXEC   sp_addextendedproperty 'MS_Description', 'Accident Safety Violation Description', 'user', dbo, 'table', 'tblSafety_Log', 'column', ASV_Descrip

EXEC   sp_addextendedproperty 'MS_Description', 'Lost Time Y/N', 'user', dbo, 'table', 'tblSafety_Log', 'column', Lost_Tm

Open in new window

0
 

Author Closing Comment

by:edrz01
ID: 34105473
Although Shaun's solution was correct, it was confusing. HainKurt also your link was a good solution, but it too was minimal confusing...but does give other examples that I now know is available.

dkrollCTN, you solution was the same as the other two...but it gave the solution AND a clearer steps to follow. And that is why you will receive the most points.

All in all... all the solutions were on target. What also hhelps the person who submits a question is to give clear understandable solutions. That is the best comment I can give.

Nevertheless...you folks here on EE are without question the BEST source and experts... Thank You...
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now