0
Fixed

SQL generated for SqlServer doesn't have correct schema name when dealing with extended properties for comments

Nick Rossiter 2 years ago updated 2 years ago 3

Hi, I've notice a bug with Sql generation.


When the target Database is SqlServer, the generated SQL for the table and column comments is not quite right if the schema being used is not dbo.


e.g. this is the generated code for the table 'MyTable' in the schema 'MySchema'.

if exists (select * from ::fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'TABLE', 'MyTable', null,null))
BEGIN
EXEC sp_dropextendedproperty
@name = N'MS_Description',
@level0type = N'SCHEMA',
@level0name = 'MySchema',
@level1type = N'TABLE',
@level1name = 'MyTable';
END;

EXEC sp_addextendedproperty
@name = N'MS_Description',
@value = N'Comment for MyTable',
@level0type = N'SCHEMA',
@level0name = 'MySchema',
@level1type = N'TABLE',
@level1name = 'MyTable';


The calls to sp_dropextendedproperty and sp_addextendedproperty are correct.

The 'if exists (select * from ::fn_listextendedproperty' call to determine whether the extended property exists is incorrect in that it has the schema as 'dbo' instead of 'MySchema'

Therefore the extended property is not dropped and the add fails as it already exists.

(This is the behaviour for when the target is SqlServer 2017/2019, I haven't checked other SqlServer versions.)

Please can you look into it

Thanks

Nick

GOOD, I'M SATISFIED
Satisfaction mark by Nick Rossiter 2 years ago