Microsoft® SQL Server™ supports two ways of commenting:
1) Line comments
Line comments starts with two dashes (-) and are terminated by the newline character (carriage returns). Everything between two dashes (-) and carriage returns is counted as comment. This comments can be placed anywhere in the sql code. This comment can be placed after your SQL code in the same line, everything before the two dashes (-) will execute like normal T-SQL, everything after two dashes (-) will be considered as comment and not executed provided they are not in the next line.
Example:
SELECT * FROM Blogs -- This table name
WHERE BlogID > 10 ---This is filter condition
2) Block comments
Block comments starts with a forward-slash followed by asterisk (/*) and ends with asterisk followed by forward-slash (*/). Everything between these two i.e. (everything between /* and */) is considered as comments. This style of commenting is actually used to describe big chunk of code and are usually placed before or after big chunk of code, however you are free to place it anywhere in your code.
Example:
/* This is example of blog comment
Following table is example of retrieve blogs with filter condition*/
SELECT * FROM Blogs
WHERE BlogID > 10
Shortcuts for commenting
| Action | Standard |
|
Make the selected text a comment | CTRL+K, CTRL+C |
|
Uncomment the selected text | CTRL+K, CTRL+U |
Why Extended Properties and Types of Extended Properties
It may happen that you want to add comment (Caption) for each column or each table so as to describe that table/Column. SQL server has provided us a list of inbuilt stored procedures to achieve this purpose. These stored procedures are:
sp_addextendedproperty adds an extended property to a database object
sp_dropextendedproperty removes an extended property from a database object
sp_updateextendedproperty updates an existing extended property value
fn_listextendedproperty retrieves the value of an extended property or the list of all extended properties of a database object.
Using extended property to add description for a column:
EXECUTE sp_addextendedproperty N'MS_Description', 'UserName of user', N'user', N'dbo', N'table', N'UserInfo', N'column', N'UserName'
(Note that this is to ADD, there is a different SP to UPDATE)
From above we can see that the call to create the extended property has eight parameters. The first two describes the name and initial value for the property accordingly. The other six are the instructions to SQL Server for locating the appropriate database object: in this case, the CompanyName column in the User table owned by the dbo user.
Similarly, we can use extended property to add description for a table:
EXECUTE sp_addextendedproperty N'MS_Description', 'Table User', N'user', N'dbo', N'table', N'UserInfo', NULL, NULL
Note that the last two parameters which are use to locate the column are passed as null.
These stored procedures can be used not only for adding descriptive comments but also to add other properties like backcolor, forecolor etc.
For example:
sp_addextendedproperty 'BackColor', 'Blue', 'user', 'dbo', 'table', 'UserInfo', 'column', 'UserName'
sp_addextendedproperty 'ForeColor', 'Yellow', 'user', 'dbo', 'table', 'UserInfo', 'column', 'UserName'
As with other properties, to update the values of extended properties, we can use the sp_updateextendedproperty stored procedure.
Example:
sp_updateextendedproperty 'BackColor', 'Blue', 'user', 'dbo', 'table', 'UserInfo', 'column', 'UserName'
sp_ updateextendedproperty 'ForeColor', 'Yellow', 'user', 'dbo', 'table', 'UserInfo', 'column', 'UserName'
If you wanna enter some dynamic description, then you can try something like this
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 'This is my table comment',
'user', @CurrentUser, 'table', 'UserInfo'
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', ‘This is the primary key comment','user', @CurrentUser, 'table', 'TABLE_1', 'column', 'ID'
List out all extended properties of your table/Column
You can get list of all extended properties of a table or column by using fn_listextendedproperty stored procedure.
Example:
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'UserInfo', 'column', 'UserName')
Output is
Note that adding/updating/Dropping these extended properties can also be done by using SSMS GUI.
As I said above, these stored procedures can be used not only to add description property but also to add other properties like caption, mask etc
Below are some of the examples:
Adding an extended property to a database
The following example adds the property 0 'Caption0' with a value of 0 ''My Sample Database' to your database.
--Add a caption to your Database object.
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'My Sample OLTP Database';
Since we have not passed any table (3-6) or Column (7-8) parameters, this property will be applied to your database.
Adding an extended property to column of your table
If we want to add caption to the column of your table, then we need to pass all the parameters as shown below:
EXEC sp_addextendedproperty
@name = N'Caption',
@value = 'UserInfo is a mandatory column.',
@level0type = N'Schema', @level0name = 'user',
@level1type = N'Table', @level1name = 'UserInfo',
@level2type = N'Column', @level2name = 'UserName';
Above example adds a caption property to column UserName of table UserInfo.
Adding an input mask property to a schema
If we are passing the first four parameters, then the property will be applied to schema as shown below
EXECUTE sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Contains objects related to users.’
@level0type = N'SCHEMA',
@level0name = 'Users';
The following example adds an extended property to the users schema.
Adding an input mask property to a column
The following example adds an input mask property '999999 or ######' to the column PinCode in the table Address.
EXECUTE sp_addextendedproperty
@name = N'Input Mask ', @value = '999999 or ######',
@level0type = N'Schema', @level0name = 'User',
@level1type = N'Table', @level1name = 'Address',
@level2type = N'Column',@level2name = 'PinCode';
1
Adding an extended property to a filegroup1
We can also use extended property to add description to the 0 PRIMARY filegroup of your database.
EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'Primary filegroup for your sample database.',
@level0type = N'FILEGROUP', @level0name = 'PRIMARY';
1
Adding an extended property to a role1
The following example creates an application role and adds an extended property to that created role.
CREATE APPLICATION ROLE Buyers
WITH Password = '987G^bv876sPY) Y5m23’;
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Application Role for User.’
@level0type = N'USER',
@level0name = 'Buyers';
1
Adding an extended property to a type1
The following example adds an extended property to a type.
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Data type (alias) to use for any column that represents an userid.’
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TYPE',
@level1name = N'userid';
1 Adding an extended property to a user1
The following example creates a user and adds an extended property to that created user.
CREATE USER CustomApp WITHOUT LOGIN;
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'User for an application.’
@level0type = N'USER',
@level0name = N'CustomApp';
Note: Images used on this website
are either a production of Bhaktivedanta Book Trust(https://www.krishna.com), Iskcon
Foundation or were found in google search under "Free to use and share". If any
of the images presented here violates copyright issues or infringes anyone copyright
or are not under "Fair use", then please bring it to our notice. Read
Disclaimer for more.
Share this to your friends. One of your friend is
waiting for your share.