locked
How to get rid of the Brackets associted with the Column Names RRS feed

  • Question

  • Hello,

    I have a question regarding T-SQL. When I right click the table in my object explorer and click on select top 1000 rows I will get something like this :

    SELECT TOP 1000 [Column1]

                                 [column2]

    FROM [dbo.Test]

    I dont want these brackets to get displayed. Does anyone know how to get this query without square brackets for the columns?.This might be silly question but we have that requirement.

    Thanks for the help.

     

    Wednesday, November 30, 2011 3:49 PM

Answers

  • I don't think there is a setting controlling the behavior in SSMS. I also found it annoying, but I guess the quickest solution will be to find [ and replace with nothing and then for the ]. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Thursday, December 1, 2011 10:11 PM
    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:41 PM
    Thursday, December 1, 2011 1:32 AM
  • Just having fun:

    WITH
    	Data
    AS
    	(
    	 SELECT
    		'dbo'	Schema_Name, 
    		'Test'	Table_Name
    	)
    SELECT
    	'SELECT TOP(1000)'
    UNION ALL
    SELECT
    	Column_Name
    	+ ','
    FROM
    	INFORMATION_SCHEMA.COLUMNS ISC,
    	Data
    WHERE
    	ISC.TABLE_SCHEMA	= Data.Schema_Name
      AND	ISC.TABLE_NAME		= Data.Table_Name
    UNION ALL
    SELECT
    	NULL
    UNION ALL
    SELECT
    	+ 'FROM '
    	+ Schema_Name
    	+ '.'
    	+ Table_Name
    	+ ';'
    FROM
    	Data;
    


    • Proposed as answer by Hasham NiazEditor Thursday, December 1, 2011 10:10 PM
    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:42 PM
    Thursday, December 1, 2011 6:00 PM

All replies

  • Are you logged in as the dbo?
    Thursday, December 1, 2011 1:29 AM
  • I don't think there is a setting controlling the behavior in SSMS. I also found it annoying, but I guess the quickest solution will be to find [ and replace with nothing and then for the ]. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Hasham NiazEditor Thursday, December 1, 2011 10:11 PM
    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:41 PM
    Thursday, December 1, 2011 1:32 AM
  • I don't think there is an option in SSMS to turn it off. The reason for the brackets to handle column names like Shipping Date (notice the space).

    If I know that the column names are plain Pascal case (like SalesOrderHeader), I just mass replace the brackets with nothing.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Thursday, December 1, 2011 1:40 AM
  • @samlucas002: Yes I logged in as dbo.

    @SQLUSA and @Naomi : Thanks for you response. Yeah I really dont like those brackets with the query.Its really annoying

    Thursday, December 1, 2011 4:22 PM
  • Just having fun:

    WITH
    	Data
    AS
    	(
    	 SELECT
    		'dbo'	Schema_Name, 
    		'Test'	Table_Name
    	)
    SELECT
    	'SELECT TOP(1000)'
    UNION ALL
    SELECT
    	Column_Name
    	+ ','
    FROM
    	INFORMATION_SCHEMA.COLUMNS ISC,
    	Data
    WHERE
    	ISC.TABLE_SCHEMA	= Data.Schema_Name
      AND	ISC.TABLE_NAME		= Data.Table_Name
    UNION ALL
    SELECT
    	NULL
    UNION ALL
    SELECT
    	+ 'FROM '
    	+ Schema_Name
    	+ '.'
    	+ Table_Name
    	+ ';'
    FROM
    	Data;
    


    • Proposed as answer by Hasham NiazEditor Thursday, December 1, 2011 10:10 PM
    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:42 PM
    Thursday, December 1, 2011 6:00 PM
  • In ANSI SQL, we use double quotes. In T-SQL dialect they use square brackets and single quotes.  this is one of many reasons that we SQL people call T-SQL a "lesser SQL" compared to DB2, Oracle, etc.  They need to fix this and a few other simple things. 

    Create a simple word processor  script top clean up MS dialect. It will still compile in MS products. 

     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Thursday, December 1, 2011 11:57 PM