Extended properties in SQL Server

[AnielaN994]

Hello,

I have some extended properties created manually in the Azure SQL DB and when I inspect the database in the CLI with below command and I check the generated sql schema, they aren't there.

$ atlas schema inspect -u "azuresql://<hostname>.database.windows.net?fedauth=ActiveDirectoryDefault&database=<DBname>&mode=database" --format '{{ sql . }}' > schema1.sql

An example of adding an extended property:
EXEC sys.sp_addextendedproperty @name=N'<NAME>', @value=N'<VALUE>'

Are extended properties supported in atlas ?

Thanks.

[giautm]

Hello, we don't support inspect / modify the extended properties yet. Can you please share your use-case? What's the suppose of those properties?

[AnielaN994]

We have a database that we deploy on multiple servers for multiple clients and based on each client, we set a specific VALUE for an extended property and depending on it, we are setting some flags that we use in our stored procs that would run specific code in the SP based on each client.

[giautm]

Got it, interesting use-case. I can add this support in up coming weeks.

[AnielaN994]

Yes please.
Thanks you.

[giautm]

Can you please listing kind of resources (table, view...) you have the Extended Property on it?

[AnielaN994]

Not sure about your question.
The extended properties are in sys.extended_properties https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/extended-properties-catalog-views-sys-extended-properties?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current.

They can be created with:
EXEC sys.sp_addextendedproperty @name=N'ExampleFlag', @value=N'TRUE' GO

and dropped with:

EXEC sys.sp_dropextendedproperty @name=N'ExampleFlag' GO

Then in our stored proc, for example we can do:

declare @ExampleFlag bit = 0
if exists (SELECT 1 FROM sys.extended_properties where [name]='ExampleFlag' and [value] = N'TRUE')
begin
set @ExampleFlag= 1
end;

if @ExampleFlag = 1
begin .....

or in a query: CASE WHEN @ExampleFlag = 1 THEN....

We have multiple flags like this depending on which we are executing specific sql code.

[giautm]

I'm asking about the class_desc of the extended property. If you don't provider it (@level0type) in the sp_addextendedproperty proc, the extended property will be add in the database scope. And unfortunately Atlas doesn't support manage objects at the Database scope yet. It only managed resources only in the schema scope. If we do support for the extended property, they're extended properties for Table/View/Proc/Func/Trigger - But not database scope.

[AnielaN994]

Hello,

Yes, they are in the DATABASE scope. Will these be added in feature releases ?

Thanks.