4

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.

1
  • SSMS shouldn't delimit INT in your example because it's a datatype, not an identifier...
    – gbn
    Sep 14, 2011 at 4:32

4 Answers 4

3

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.

1

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
6
  • 1
    The first option can be tedious if you have a lot of different types, and the second can be problematic if you actually do want or even need the square brackets around column names, table names, etc. Sep 13, 2011 at 22:37
  • That gets the list, now what about the actual replace? Keep in mind your audience may not know what a truth editor is, despite your link. You also have to account for keyword column names, which are quite common, such as [date], case sensitive collations, etc. I think brute force replace is a lot more tedious than you think. Sep 13, 2011 at 23:38
  • @Aaron Bertrand I don't know what are you talking about, really :), sorry, friend.
    – garik
    Sep 13, 2011 at 23:43
  • You're telling the guy to run macros against the script that management studio produces when he scripts a table, right? Or are you solving a different problem? Sep 13, 2011 at 23:45
  • @Aaron Bertrand I have just suggested workaround. I can suggest to write small program by using SQL Server SMO scripting and then replace all these types with []less data types :), Is it ok, now? Is this available for audience of this site? So I suggested cheap and simple one-time way.
    – garik
    Sep 14, 2011 at 0:04
0

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

3
  • Will, Thanks for your reply. However, I should have stated that I had done my basic due diligence and gone through the SSMS 101. I have followed that very path and was not able to find a setting that appears to affect this particular issue. Can you point me to the setting I might have missed? Sep 13, 2011 at 21:34
  • Ack, you are correct. I saw the delimiter option but realized after the fact that it was for batching the statements and not the brackets. It appears that MS believes that the square brackets are a good practice and have not put any option in to remove them from the generated scripts.
    – Wil
    Sep 13, 2011 at 22:12
  • Granted, the square brackets for data types specifically are not exactly a decent practice unless you're already in the bad practice of using reserved or otherwise bad words for user-defined data types. :-) Sep 13, 2011 at 22:36
0

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'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.