How to add command description

Posted by Arun_Ch under Sql Server on 4/9/2012 | Points: 10 | Views : 946 | Status : [Member] | Replies : 2
Hi ,
In SQL Server 2005 how to add commands description while creating a table...

Thanks & Regards
Arunkumar



Responses

Posted by: Pandians on: 4/9/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

You can use Extended Properties...

- We have created one table called "Tbl_Sample" which having 3 columns (ID, Name and Age).
- We have given comments/description for ID and Name columns...

CREATE TABLE Tbl_Sample(

[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL
)
GO
EXEC sys.sp_addextendedproperty @name=N'Comment1',
@value=N'"ID" column is used to maintain unique data' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Tbl_Sample',
@level2type=N'COLUMN',
@level2name=N'ID'
GO
EXEC sys.sp_addextendedproperty @name=N'Comment1',
@value=N'"Name" column is used to maintain EmployeeName' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'Tbl_Sample',
@level2type=N'COLUMN',
@level2name=N'Name'
GO
To fetch the Extended Properties
SELECT OBJECT_NAME(major_id) [Table], COL_NAME(major_id,minor_id) [Column], Value FROM sys.extended_properties

WHERE Class=1
AND major_id = OBJECT_ID('Tbl_Sample')


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Arun_Ch, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/9/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,
-- for single line comment
/*......*/ for multiline comment

Regards,
Singaravel M

Arun_Ch, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response