Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.

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
add comment

4 Answers

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

share|improve this answer
add comment

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
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
show 1 more comment

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
add comment

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'
share|improve this answer
add comment

Your Answer

 
discard

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.