-- SQL batch delete all field description attributes in a specified data table
-- Symptom: Execute select * From SYS. extended_properties where value is null. Multiple records are displayed, similar to [1 object_or_column 2372069 1 ms_description null].
-- Operation: Execute Delete from SYS. extended_properties where value is null. The system directory cannot be updated on demand.
-- Function Description: due to the incorrect operation, the field description is null and added to the system data table SYS. extended_properties. In the end, the field description must be deleted in batches before adding it.
Declare @ tablename nvarchar (250) -- defines the data table name variable of the current operation
Set @ tablename = 'user' -- specify the name of the data table to be queried (the data table name user here needs to be updated to the existing data table name in your database)
Declare @ columnname nvarchar (250) -- Name of the field currently queried in the cursor
Declare @ columndescription nvarchar (250) -- Description of the fields currently queried in the cursor
-- Declare the cursor for reading all fields in the data table
Declare mycursor cursor for select. name, cast (G. value as nvarchar) from sys. columns A left join sys. extended_properties g on (. object_id = G. major_id and. column_id = G. minor_id) Where object_id = object_id (''+ @ tablename +'') order by object_id,. column_id
-- Open the cursor
Open mycursor
-- Retrieve the data from the cursor and assign values to the constraint name variable.
Fetch next from mycursor into @ columnname, @ columndescription
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin
If (@ columndescription is null)
Begin
Print 'the description of the current data table ['+ @ tablename +'] field ['+ @ columnname +'] is blank'
-- Delete a record whose descriptive attribute value is null.
-- Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table', 'Data table name', 'column ', 'field name'
Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table', @ tablename, 'column', @ columnname
End
Else
Begin
-- Query the field description currently found
The description of the print 'current data table ['+ @ tablename +'] field ['+ @ columnname +'] is ['+ @ columndescription +'] '.
End
-- use a cursor to retrieve the next record
fetch next from mycursor into @ columnname, @ columndescription
end
-- close the cursor
close mycursor
-- undo the cursor
deallocate mycursor