Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


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:


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,


share|improve this question
SSMS shouldn't delimit INT in your example because it's a datatype, not an identifier... – gbn Sep 14 '11 at 4:32
up vote 3 down vote accepted

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 or to Mladen for including in his SSMS Tools Pack.

share|improve this answer

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.


select '[' + UPPER(name) + ']' from sys.types
share|improve this answer
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. – Aaron Bertrand Sep 13 '11 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. – Aaron Bertrand Sep 13 '11 at 23:38
@Aaron Bertrand I don't know what are you talking about, really :), sorry, friend. – garik Sep 13 '11 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? – Aaron Bertrand Sep 13 '11 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 '11 at 0:04

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

share|improve this answer
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? – Eric Liprandi Sep 13 '11 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 '11 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. :-) – Aaron Bertrand Sep 13 '11 at 22:36
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Marian Sep 18 '12 at 19:40

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 (...)
    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
        MIN(column_id) as column_id,
        CanUpdate, FieldName, PrimaryKey, FieldType, FieldLength, FieldPrecision, FieldScale
            MIN(ISNULL(convert(int, ic.is_included_column),case when sc.is_identity = 1 then 0 else 1 end)) as CanUpdate,
   as FieldName,
            max(case when ind.is_primary_key = 1 and ic.is_included_column = 0 THEN 1 ELSE 0 END) as PrimaryKey,
   as FieldType,
            sc.max_length as FieldLength,
            sc.precision as FieldPrecision,
            sc.scale as FieldScale
            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
            OBJECT_NAME(sc.object_id) = @TableName
        group by
            ) 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 */


CREATE TABLE [dbo].[' + @TableName + '](' + @FieldDefinition + ',
    ' + PKFields + '

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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