SQL Query with quoted identifier

i'm trying to use the sp_estimate_data_compression_Savings

to get all tables;

i'm getting an error:

i want it to look like this:

EXEC sp_estimate_data_compression_savings 'dbo', 'fact_WebActivity', NULL, NULL, 'ROW' ;

Trying this: so that i get all tabels

select 'EXECUTE sp_estimate_data_compression_savings ''dbo''' + ',' + ''''+ name from sys.tables where type = 'u' + '''
Auerelio VasquezETL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You have to prefix every tick (  '  ) with another, and then wrap the thing in ticks!

declare @str varchar(1000)
set @str = 'sp_estimate_data_compression_savings '
set @str = @str + '''dbo'', '
set @str = @str + '''fact_WebActivity'' '
set @str = @str + ', NULL, NULL, ''ROW'' ;'
--print @str       --do this to check that it's right !
exec @str

I did it in four steps to show how more clearly.
select 'EXEC sp_estimate_data_compression_savings ''dbo,''' + name  + ''',NULL,NULL,''ROW''' from sys.tables where type = 'u'

Open in new window

(oops - missed parens around the @str - sorry!)
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Oh, I thought you were trying to build a literal set of command lines, to execute later (based on your SELECT statement shown).

The select statement I posted above produces the following output...

EXEC sp_estimate_data_compression_savings 'dbo,'r3_tracker-20110926_baseline',NULL,NULL,'ROW'
EXEC sp_estimate_data_compression_savings 'dbo,'srcBL_GroupMembers',NULL,NULL,'ROW'
EXEC sp_estimate_data_compression_savings 'dbo,'r3_tracker-20110926_rit3010422',NULL,NULL,'ROW'
EXEC sp_estimate_data_compression_savings 'dbo,'srcBL_Groups',NULL,NULL,'ROW'
EXEC sp_estimate_data_compression_savings 'dbo,'r3_tracker-20110927_rit301(2725-3311-3393)',NULL,NULL,'ROW'
EXEC sp_estimate_data_compression_savings 'dbo,'srcBL_InteractiveRights',NULL,NULL,'ROW'

Open in new window

Auerelio VasquezETL DeveloperAuthor Commented:

You are correct in your assumption. That's what i wanted. The only issue:

I need the second tick mark around dbo like 'dbo'

to look like this

EXEC sp_estimate_data_compression_savings 'dbo','fact_WebActivity',1,NULL,'Page'
Whoops. Put two ticks after dbo, before the period. That should do it.
Auerelio VasquezETL DeveloperAuthor Commented:
ok. so this worked .  i was overthinking it. One last question regarding this, how is the best way, to get each line returned from all of these exec statements, into one table ?

select 'EXEC sp_estimate_data_compression_savings ''dbo'',''' + name  + ''',NULL,NULL,''ROW''' from sys.tables where type = 'u'
select 'EXEC sp_estimate_data_compression_savings ''dbo'',''' + name  + ''',NULL,NULL,''ROW''' as SQLCMD into doCommands from sys.tables where type = 'u'

Open in new window

...will create a table named doCommands with a single column (SQLCMD) where each row is one command returned from the query.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Auerelio VasquezETL DeveloperAuthor Commented:
I apologize, what i meant, was the results from all of those queries, into one table, one row at a time. this give all the commans, which is how i asked it, but i wanted the results. these come out one execution at a time. is there a way, to that result, and insert into a table. i thought of a cursor for this, but i can't get my hands around all the tick marks, to put into a @String varable.....
Auerelio VasquezETL DeveloperAuthor Commented:
i can put this into another question, so i can award these points, if you like.
A new topic would probably be best, to help those looking for specific answers in the future.
Auerelio VasquezETL DeveloperAuthor Commented:
Can I direct that to u somehow?
I'll check your profile for open questions later this afternoon... but there may be better folks to provide you with a quick answer.

Thanks for the vote of confidence!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.