edrz01
asked on
Create Table - entering a Column Descripton
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...
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
ASKER
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...
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...
ASKER