0
Fixed

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

Nick Rossiter 3 aastat tagasi uuendatud 3 aastat tagasi 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

HÄSTI, OLEN RAHUL
Satisfaction mark by Nick Rossiter 3 aastat tagasi
Fixed

It is fixed. Please check if it works for you.

Yes, it works for me. Thanks for the quick turnaround!

Nick