Shulou(Shulou.com)05/31 Report--

How to add annotation information to the index in SQL Server, for this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

How to add comment information to the index of SQLServer by extending properties

Select an index: attribute, extend attribute, and add "description" and "practical use of this index" to the name and value of the table.

If an index does several things at the same time, you can add comments like this:

(1) when inserting data, judge whether the specified data exists according to date+code. (2) the background calculates the summary data for a period of time according to date.

In this way, when others are not clear about the role of an index, just take a look at the extended properties of the index.

To view a list of index comments in a database, you can query it like this:

Selectobject_name (major_id) astable_name, (selectnamefromsys.indexeswhereobject_id=major_idandindex_id=minor_id) asindex_name,name,valuefromsys.extended_propertieswhereclass_desc='INDEX'

In the process of development, we usually specify comments for each index on the development database. If you want to synchronize the comments here to the production server, you can do this through script:

EXECsp_addextendedproperty@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level2type=N'INDEX',@level1name=N' table name', @ level2name=N' index name', @ name=N' description', practical use of @ value=N' index'

How to add comment information to the index of SQLServer by extending properties

Update: sp_addextendedproperty

Delete: sp_dropextendedproperty

Calling the sp_ stored procedure here can complete the task, but it is a bit tedious. You can create a stored procedure by yourself to add comments to the index:

-- sets the extended property for the specified index of the specified table (the default extended property name is: 'description') to facilitate comments for the index

ALTERPROCEDURE [dbo] .SetIndexDesc @ tablenamenvarchar, @ indexnamenvarchar, @ descvaluenvarchar (500) ASBEGIN

-first check whether there is comment information on the index, and if so, delete it first.

Ifexists (select*fromsys.extended_propertieswhereclass_desc='INDEX'andobject_name (major_id) = @ tablenameand (selectnamefromsys.indexeswhereobject_id=major_idandindex_id=minor_id) = @ indexname) beginEXECsp_dropextendedpropertyN' description', Noble _ schema _ repartee _ indexnamematch _ end

-create comment information on the index

EXECsp_addextendedproperty@level0type=N'SCHEMA',@level0name=N'dbo',@level1type=N'TABLE',@level2type=N'INDEX',@level1name=@tablename,@level2name=@indexname,@name=N' description', @ value=@descvalueEND

When called:

ExecSetIndexDesc' table name', 'index name', N 'comment information'

If you need to synchronize the list of index comments for the development database to the production server, you can do this:

Select'execSetIndexDesc'''+object_name (major_id) +'',''+ (selectnamefromsys.indexeswhereobject_id=major_idandindex_id=minor_id) +'', N'''+convert (nvarchar (500), value) + 'fromsys.extended_propertieswhereclass_desc='INDEX'andname=N' description'

List the output scripts and execute them on the production server.

This is the answer to the question about how to add comments to the index in SQL Server. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.