SQL Server gets, adds, modifies, deletes field descriptions

Get, add, modify, delete SQL Server field descriptions

Select O.name as TableName, c.name as ColumnName, P.[value] as Description

From Sysproperties P INNER JOIN

sysobjects o on o.id = p.id INNER JOIN

syscolumns C on p.id = c.id and p.smallid = C.colid

Where (p.name = ' ms_description ') and (c.name = ' province ') and (o.name = ' ip_address ')

ORDER BY O.name

Select the description of the field province in table IP_Address

--Create tables and descriptive information

Create TABLE table (A1 varchar (), a2 char (2))

--Add descriptive information to the table

EXECUTE sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' user ', n ' dbo ', n ' table ', n ' tables ', NULL, NULL

--Add descriptive information for field A1

EXECUTE sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' watch ', n ' column ', n ' A1 '

EXECUTE sp_addextendedproperty n ' ms_description ', ' Test ', n ' user ', n ' dbo ', n ' table ', n ' hr_employees ', n ' column ', n ' Test '

--Add descriptive information for field A2

EXECUTE sp_addextendedproperty n ' ms_description ', ' sex ', n ' user ', n ' dbo ', n ' table ', n ' tables ', n ' column ', n ' A2 '

--Update the description properties of the column A1 in the table:

EXEC sp_updateextendedproperty ' ms_description ', ' Field 1 ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1

--Deletes the description property of the column A1 in the table:

EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1

--Delete test

drop TABLE Table