import extended properties of sql server database from xls file

Question

I have a list of extended properties for all databases in an excel file. How can I import that into extended properties of each database in sql server.

Answer 1

The TSQL script way of adding an Extended property is via sp_addextendedproperty

Slightly altered example from the documentation:

USE AdventureWorks2022;
GO

EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = 'Postal code is a required column.',
    @level0type = 'SCHEMA', @level0name = N'Person',
    @level1type = 'TABLE', @level1name = N'Address',
    @level2type = 'COLUMN', @level2name = N'PostalCode';
GO

While you could create the statement in Excel through string concatenation that would be mainly useful for a single database only.

If you have multiple databases you might be better off using some programming language outside of SQL like PowerShell (perhaps with dbatools module) and looping through the rows while passing the values to the proc above.