Tables are an essential component of any relational database management system, and Microsoft SQL is no exception. With the ability to store and organize large amounts of data in a structured format, tables play a crucial role in the functionality and efficiency of SQL databases.
One commonly used feature of SQL tables is the ability to add a description or comment to a table. This can provide valuable information for database administrators and developers, making it easier to understand the purpose and structure of a table. In this article, we will explore how to add a description or comment to a table in Microsoft SQL 2000 and above.
To begin, let's first understand the importance of adding a description or comment to a table. As databases grow in size and complexity, it becomes increasingly challenging to keep track of the purpose and relationships between tables. A well-written description or comment can serve as a quick reference to understand the purpose of a table and its columns. It can also provide helpful information for future modifications or troubleshooting.
In Microsoft SQL 2000 and above, there are two ways to add a description to a table. The first method is to use the sp_addextendedproperty system stored procedure. This procedure allows us to add extended properties to database objects, including tables. The syntax for using this procedure is as follows:
sp_addextendedproperty [ @name = ] 'property_name'
, [ @value = ] 'property_value'
, [ @level0type = ] 'level0_object_type'
, [ @level0name = ] 'level0_object_name'
, [ @level1type = ] 'level1_object_type'
, [ @level1name = ] 'level1_object_name'
, [ @level2type = ] 'level2_object_type'
, [ @level2name = ] 'level2_object_name'
Let's break down the syntax to understand it better. The @name parameter specifies the name of the extended property we want to add, while the @value parameter contains the actual description or comment. The @level0type, @level0name, @level1type, @level1name, @level2type, and @level2name parameters identify the object to which we want to add the extended property. In the case of tables, we will use the following values:
@level0type = 'schema',
@level0name = 'database_schema_name',
@level1type = 'table',
@level1name = 'table_name'
For example, if we want to add a description to a table named "Customers" in the "Sales" schema, the syntax will be as follows:
sp_addextendedproperty @name = 'Description'
, @value = 'This table contains customer information'
, @level0type = 'schema'
, @level0name = 'Sales'
, @level1type = 'table'
, @level1name = 'Customers'
The second method to add a description to a table is to use the Properties window in Microsoft SQL Server Management Studio. To do this, right-click on the table and select "Properties." In the "Extended Properties" tab, click on the "Add" button and fill in the required information, including the name and value of the extended property.
Once the description or comment is added, it can be accessed through the sys.extended_properties catalog view. This view contains information about all the extended properties added to database objects
