• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

problem with quotes

the below works.. but i want to put it inside a sp_msforeachdb... then the quotes get haywire.. can you suggest the right syntax to get this into the proc?

select 'select '+ ''''+ db_name() + ''','+ ''''+ table_name + ''''+ ',max('+column_name+') from '+  table_name+' union' from ic.information_schema.columns where column_name like '%Post%Date'

thanks
0
25112
Asked:
25112
  • 4
  • 3
1 Solution
 
Eugene ZCommented:
try


exec sp_msforeachdb 'print ''?'' use  ? select db_name() , table_name,column_name  from information_schema.columns where column_name like ''%Post%Date'''
0
 
Scott PletcherSenior DBACommented:
Just "cheat", like below, by using double quotes instead of single around the entire SELECT string you want to execute:


go
set quoted_identifier off

exec sp_MSforeachdb " --<<-- dbl quote, not two single quotes
select 'select '+ ''''+ db_name() + ''','+ ''''+ table_name + ''''+ ',max('+column_name+') from '+  table_name+' union'
from information_schema.columns ic
where column_name like '%Post%Date'
" --<<-- dbl quote, not two single quotes
go
set quoted_identifier on
go
0
 
25112Author Commented:
that is smart!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Eugene ZCommented:
25112: just FYI -- dbl quote may not always work ..   in order to make it work you need, in some cases, to do extra ...

and BTW:  'select '+ ''''+ db_name() + ''','+ ''''+ table_name + ''''   -> ?  really?
0
 
Scott PletcherSenior DBACommented:
>> just FYI -- dbl quote may not always work <<

ONLY if you had double quotes w/i the original string -- not very likely here.

I use this trick all the time, since it's MUCH easier than adding all the extra single quotes correctly.



>>  really? <<

Yes, of course.  He wants to SELECT the db name and the table name as part of the result set.  Since these queries are dynamic, how else would he know the source of the results for that row??
0
 
Eugene ZCommented:
<ONLY if you had double quotes w/i the original string -- not very likely here.>
How do you know  likelly or not ?
---
also

1st check SET QUOTED_IDENTIFIER +
 -

< He wants to SELECT the db name and the table name as part of the result set>
2nd  -- see my above post  -- it is probably what he needed as result

3rd -- try to run your code
0
 
Scott PletcherSenior DBACommented:
>> <ONLY if you had double quotes w/i the original string -- not very likely here.>
How do you know  likelly or not ? <<

Hmm, because I SAW the code and didn't see any dbl quotes.  And typically when querying data from a table, you NEVER need to use dbl quotes, only single quotes.


>> 3rd <<

I DID run the code, and it worked fine for me.
0
 
Eugene ZCommented:
ok
I finaly rechecked  this code and tested - it is good  -->maybe requestor had something in his mind about need to run it...
I somehow missed select inside select -- probably " is the simple way for this case - specially he likes it
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now