Keywords, Brackets, Identifiers and Ridiculous.

One of my DBAs recently reported to me that our indexing job was failing because a database included in the re-indexing code was actually named after a T-SQL keyword. I think I actually guffawed, not because it was impossible, but just because this “actually” happened. So, being inquisitive, I took this whole idea that a database could be named a keyword to the next few levels. How about tables, fields and the data itself? What follows is the query to create the “SELECT” database and a couple of keyword-named tables. It is an excercise in the ridiculous for sure, but makes one wonder. What if you failed to identify your objects with brackets or quoted identifiers?

Query to build SELECT database. Also, this script was created from the new “Script Data” section of the SSMS 2008 “Generate Scripts” wizard to save a few steps (see previous blog).

–BEGIN SCRIPT

USE [master]

GO

/****** Object: Database [SELECT] Script Date: 12/08/2008 22:15:22 ******/

CREATE DATABASE [SELECT] ON PRIMARY

( NAME = N’SELECT’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATASELECT.mdf’ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’SELECT_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATASELECT_1.ldf’ , SIZE = 9216KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [SELECT] SET COMPATIBILITY_LEVEL = 90

GO

USE [SELECT]

GO

/****** Object: Table [dbo].[IN] Script Date: 12/08/2008 22:15:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[IN](

[BETWEEN] [nchar](25) NULL,

[LIKE] [nchar](10) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’AVG ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’ABS ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’AND ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’BACKUP ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’BEGIN ‘, N’END ‘)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’BETWEEN ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’CHECKPOINT ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’HAVING ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’EXIT ‘, NULL)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’IF ‘, N’ ‘)

INSERT [dbo].[IN] ([BETWEEN], [LIKE]) VALUES (N’LIKE ‘, N’LIKE ‘)

/****** Object: Table [dbo].[FROM] Script Date: 12/08/2008 22:15:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[FROM](

[Where] [int] NULL

) ON [PRIMARY]

GO

INSERT [dbo].[FROM] ([Where]) VALUES (1)

INSERT [dbo].[FROM] ([Where]) VALUES (2)

INSERT [dbo].[FROM] ([Where]) VALUES (3)

INSERT [dbo].[FROM] ([Where]) VALUES (4)

–END Script

And what do you do with a database named SELECT with tables named FROM and IN? Well of course, you populate them with other keywords and query those values. Without explanation we delve deeper with a couple of interesting queries that actually work.

The first:

select * from [Select]..[FROM] WHERE [WHERE] > 1

And finally (my favorite):

Select *

from [IN]

WHERE ( [BETWEEN] BETWEEN ‘AND’ AND ‘BETWEEN’

OR [LIKE] LIKE ‘LIKE%’

) OR [BETWEEN] IN (‘BETWEEN’)

So I could have continued on, adding stored procedures named “BEGIN” or “EXEC”…but stopped with well enough. Plus my lunch break was over. Tomorrow’s lunch: sp_who_knows?

  • 1496 views

  • Rate
    [Total: 0    Average: 0/5]