how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

Question

We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases. While it does an OK job, it bugs me that it insists on surrounding data types with []. So a column definition will be like:

[ID] [INT] IDENTITY(1,1) NOT NULL

while this just works, I don't like it because it does not distinguish the column name from its type in the script. Including syntax highlighting (everything is just black or your default font color). I was wondering if anybody had any idea on how to alter this behavior. We just want the output to be like:

[ID] INT IDENTITY(1,1) NOT NULL

Purchasing a specific tool is not an option at this time based on how much scripting we really do.

Thanks in advance for any help,

Eric.

Answer 1

I don't know of any setting which will do this in SSMS. Microsoft probably does it so that they don't need to check the data type name for special characters when generating the script (user defined data type names could have anything in them) so they just box everything.

You could submit that as a feature request to the tools team via connect.microsoft.com or to Mladen for including in his SSMS Tools Pack.

Answer 2

It seems [] is one of internal features. Just do macros in any truth editor to replace [INT], [VARCHAR]... to []less version or just replace all [ and ] with empty char.

ADDED:

select '[' + UPPER(name) + ']' from sys.types

Answer 3

Go to Tools > Options > SQL Server Object Explorer > Scripting.

Answer 4

You could roll your own scripting (I've written myself help SQL scripts using sys.objects sys.columns etc...

I use a variation of the below script to get me the field definitions, then it's just a matter of string concatenation (with setting SSMS to output to Text):

DECLARE @TableName varchar(200)

SET @TableName = '[MyTable]'

INSERT INTO @WorkingTable (...)
select
    CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale,
    case FieldType 
            when 'varchar' then '(' + case when FieldLength = -1 then 'max' else convert(varchar, FieldLength) end + ')' 
            when 'varbinary' then '(' + convert(varchar, FieldLength) + ')' 
            when 'nvarchar' then '(' + case when FieldLength = -1 then 'max' else convert(varchar, FieldLength / 2) end + ')' 
            when 'char' then '(' + convert(varchar, FieldLength) + ')' 
            when 'nchar' then '(' + convert(varchar, FieldLength / 2) + ')' 
            when 'numeric' then '(' + CONVERT(varchar, FieldPrecision) + ', ' + CONVERT(varchar, FieldScale) + ')'
            when 'decimal' then '(' + CONVERT(varchar, FieldPrecision) + ', ' + CONVERT(varchar, FieldScale) + ')'
            else '' 
        end as fieldLengthString
from
    (select 
        MIN(column_id) as column_id,
        CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale
    from
        (select 
            sc.column_id,
            MIN(ISNULL(convert(int, ic.is_included_column),case when sc.is_identity = 1 then 0 else 1 end)) as CanUpdate,
            sc.name as FieldName,
            max(case when ind.is_primary_key = 1 and ic.is_included_column = 0 THEN 1 ELSE 0 END) as PrimaryKey,
            typ.name as FieldType,
            sc.max_length as FieldLength,
            sc.precision as FieldPrecision,
            sc.scale as FieldScale
        from
            sys.columns sc 
            left join sys.indexes ind on ind.object_id = sc.object_id and (ind.is_unique = 1 or ind.is_unique_constraint = 1 or ind.index_id = 1)
            left join sys.index_columns ic on ic.object_id = ind.object_id and ic.index_id = ind.index_id and ic.column_id = sc.column_id
            left join sys.types typ on typ.user_type_id = sc.user_type_id
        where
            OBJECT_NAME(sc.object_id) = @TableName
        group by
            sc.column_id,
            sc.name,
            typ.name,
            sc.max_length,
            sc.precision,
            sc.scale
            ) t
    group by
        CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale) s
order by    column_id

so to output the create table script would be:

/* Parse up strings of fields in table, and fields in Primary key */
DECLARE @FieldDefinition varchar(max), @PKFields varchar(max)

SET @FieldDefinition = ''

SELECT @FieldDefinition = @FieldDefinition + CASE @FieldDefinition WHEN '' THEN '' ELSE ', ' END + '
    [' + wt.FieldName + '] [' + wt.FieldType + ']' + fieldLengthString
FROM @WorkingTable wt

SELECT @PKFields = @PKFields + case when @PKUpdMatch = '' THEN '' ELSE ',
        [' + wt.FieldName + '] ASC'
FROM @WorkingTable wt WHERE wt.PrimaryKey = 1

/* now print out the string for creating the table */
print 'SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[' + @TableName + '](' + @FieldDefinition + ',
 CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY NONCLUSTERED 
(
    ' + PKFields + '
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO'