ForEach Goofyness - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

ForEach Goofyness

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2011-07-28 : 09:33:08

sp_msforeachDB " SELECT QUOTENAME('?') "
Returns, as you'd expect, names like [master]

but

sp_msforeachTABLE " SELECT QUOTENAME('?') "
returns names like [[dbo]].[My_Application_Table]]]
Notice that there are 3 left brackets and 5 right brackets.

Same behavior in SQL 2000, 2008

Is this somehow usefull, or a bug?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 09:45:34

Not a bug. sp_MSforeachtable includes the 2-part name of the table, and adds the quotes/brackets automatically to ensure they resolve correctly. Databases only have 1-part names, although it's kinda annoying that they're inconsistent. But that's what you get with undocumented procedures.

dbthj
Posting Yak Master

143 Posts

Posted - 2011-07-28 : 11:50:48

Sorry, I don't follow.
Should there not still be the same number of left brackets as right?

-- ====== #1 ======
Select * from [[dbo]].[My_Application_Table]]]
Returns an error. (Invalid object name '[dbo].[My_Application_Table]'.)

-- ====== #2 ======

declare @var varchar(100)
declare @sql varchar(100)
set @var = '[[dbo]].[My_Application_Table]]]'
select @sql = 'select * from ' + @var
exec(@sql)
-- returns the same error

-- ====== #3 ======

declare @var varchar(100)
declare @sql varchar(100)
set @var = '[[dbo]].[My_Application_Table]]]'
set @var = replace(@var,']]]',']]')
set @var = replace(@var,']]',']')
set @var = replace(@var,'[[','[')
select @sql = 'select * from ' + @var
exec(@sql)

-- this works just fine.

-- and the mismatched brackets is not a bug?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 11:54:30

I admit, that does sound like a bug. But again, it's an undocumented procedure, and the fix for sp_MSforeachtable is to not use QUOTENAME or other explicit brackets.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 12:59:00

Update: it seems this is expected behavior of the QUOTENAME function: http://msdn.microsoft.com/en-us/library/ms176114.aspx

If you try the examples below you can see the effect:

select QUOTENAME('[a].[b]')
select QUOTENAME('[a.[b]')
select QUOTENAME('[a.b]')
select QUOTENAME('a.[b]')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources