· 8 years ago · Jan 23, 2018, 05:58 PM
1IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';
2
3IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';
4
5CREATE TABLE TEST
6(
7 ID INT IDENTITY(1,1) PRIMARY KEY,
8 OBJECT sysname NOT NULL
9);
10GO
11
12INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
13GO
14
15IF OBJECT_ID('TEST') IS NOT NULL
16BEGIN
17 SELECT 'TEST EXISTS.' OBJECT_ID;
18END
19GO
20
21| OBJECT_ID |
22| :----------- |
23| TEST EXISTS. |
24
25IF OBJECT_ID('[TEST]') IS NOT NULL
26BEGIN
27 SELECT '[TEST] EXISTS.' OBJECT_ID;
28END
29GO
30
31| OBJECT_ID |
32| :------------- |
33| [TEST] EXISTS. |
34
35CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX))
36RETURNS NVARCHAR(MAX)
37AS
38 BEGIN
39 RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']',
40 SUBSTRING(@TXT, 2, LEN(@TXT) - 2),
41 @TXT);
42 END;
43GO
44
45SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
46GO
47
48NAME1 | NAME2
49:---- | :----
50FIELD | FIELD
51
52SELECT ID, OBJECT
53FROM TEST
54WHERE OBJECT LIKE 'obj%';
55GO
56
57ID | OBJECT
58-: | :-----
59 2 | obj2
60 3 | obj3
61
62SELECT ID, dbo.UNQUOTENAME(OBJECT)
63FROM TEST
64WHERE dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
65GO
66
67ID | (No column name)
68-: | :---------------
69 1 | obj1
70 2 | obj2
71 3 | obj3
72 4 | obj4
73
74select @UnqualOldName = parsename(@objname, 1),
75 @QualName1 = parsename(@objname, 2),
76 @QualName2 = parsename(@objname, 3),
77 @QualName3 = parsename(@objname, 4)
78
79cmp r9d,22h
80je sqlmin!CbParseQuotesW+0x185
81cmp r9d,2Eh
82je sqlmin!CbParseQuotesW+0x139
83cmp r9d,5Bh
84je sqlmin!CbParseQuotesW+0xfe
85cmp r9d,5Dh
86je sqlmin!CbParseQuotesW+0xda
87
88IF OBJECT_ID('TEST') IS NOT NULL
89
90IF OBJECT_ID(N'dbo.TEST', N'U') IS NOT NULL
91
92drop table if exists #t1;
93
94create table #t1 (OBJ_NAME sysname);
95
96insert #t1 (OBJ_NAME) values ('[column1_name]'), ('column1_name');
97
98select * from #t1 where parsename(OBJ_NAME, 1) like 'column1%';