Script for CONSTRAINT in tables folder SOMETIMES generates with an SQL Syntax error!!!

[kirkw]

This might be some kind of "security" issue.

In the tables\Events.sql : [bigpicture] nvarchar NULL CONSTRAINT [[dbo].[Events]bigpictureDefault] DEFAULT (NULL), [story] nvarchar NULL CONSTRAINT [[dbo].[Events]storyDefault] DEFAULT (NULL),

on my machine it does it correctly (SQLEXPRESS) : [bigpicture] nvarchar NULL CONSTRAINT [bigpictureDefault] DEFAULT (NULL), [story] nvarchar NULL CONSTRAINT [storyDefault] DEFAULT (NULL),

-- I double checked that I have the same EXE -- I wish HELP would show the version :-) -- Also, it would be great if you scripted a TESTALL.SQL (switch) SET NOEXEC ON :r "<filename.sql>" -- for each file you generate!!!

I did it manually using this command: [in the hopes someone finds it useful] for /R server\ %x in (*.sql) do @Echo :r "%x"

and found 3 ERRORS, ALL the same flavor!

And I found 2 of those errors on MY tables. Very Strange...

[kirkw]

Interesting, I found another tool (dbForge) that produces SIMILAR OUTPUT, but it PASSES the test, but looks WRONG!

  MonthID float NULL CONSTRAINT DF__Events__MonthID__37A5467C DEFAULT (NULL),
  bigpicture nvarchar(50) NULL CONSTRAINT [[dbo]].[Events]]bigpictureDefault] DEFAULT (NULL),
  story nvarchar(255) NULL CONSTRAINT [[dbo]].[Events]]storyDefault] DEFAULT (NULL),
  eligibility nvarchar(max) NULL,

Notice that the number of ] just seems wrong! I have 3 open ['s and 5 Close ]'s

and that passes! NOTE: You can create one yourself, with this STUPID syntax: alter table purse add constraint [[dbo]].[purse]]eventdateDefault] default (NULL) for eventdate;

Whereas SchemaZen appears to balance them out, and that screws them up!

Somewhere, someone coded the "(NULL)" default value into the table, which creates a constraint. Hmm these should be missing, as the natural state of these is NULL for a default value! (Unless SQL actually creates invisible constraints for them,normally)

FWIW: It turns out that a TOOL used to migrate the DB years ago, probably broke this in generating it. It literally created the constraint name as: "[dbo].[Events]bigpictureDefault" and "[dbo].[Events]storyDefault"

At least that's the constraint_name from the query:

select table_view,
    object_type, 
    constraint_type,
    constraint_name,
    details
from (
    select schema_name(t.schema_id) + '.' + t.[name] as table_view, 
        case when t.[type] = 'U' then 'Table'
            when t.[type] = 'V' then 'View'
            end as [object_type],
        case when c.[type] = 'PK' then 'Primary key'
            when c.[type] = 'UQ' then 'Unique constraint'
            when i.[type] = 1 then 'Unique clustered index'
            when i.type = 2 then 'Unique index'
            end as constraint_type, 
        isnull(c.[name], i.[name]) as constraint_name,
        substring(column_names, 1, len(column_names)-1) as [details]
    from sys.objects t
        left outer join sys.indexes i
            on t.object_id = i.object_id
        left outer join sys.key_constraints c
            on i.object_id = c.parent_object_id 
            and i.index_id = c.unique_index_id
       cross apply (select col.[name] + ', '
                        from sys.index_columns ic
                            inner join sys.columns col
                                on ic.object_id = col.object_id
                                and ic.column_id = col.column_id
                        where ic.object_id = t.object_id
                            and ic.index_id = i.index_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    where is_unique = 1
    and t.is_ms_shipped <> 1
    union all 
    select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        'Table',
        'Foreign key',
        fk.name as fk_constraint_name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        inner join sys.foreign_key_columns fk_cols
            on fk_cols.constraint_object_id = fk.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Check constraint',
        con.[name] as constraint_name,
        con.[definition]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    union all
    select schema_name(t.schema_id) + '.' + t.[name],
        'Table',
        'Default constraint',
        con.[name],
        col.[name] + ' = ' + con.[definition]
    from sys.default_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id) a
			-- where a.constraint_type = 'Foreign key' -- and table_view='dbo.Money2'
      WHERE a.details LIKE '%= (NULL)'   AND constraint_name LIKE '%.%'
order by table_view, constraint_type, constraint_name;