Using SQL to Build SQL

One of the DBAs on my team hadn’t seen this trick yesterday, so I thought it would be good to document it for persistence. I particularly like to use this technique when granting access to a specific object to user or role. This technique is also known as concatenating SQL.

Here is how you do in SQL Server–if you want to grant select on all tables in a database to some user.

select ‘grant select on ‘+name+’ to someuser;’ from sys.tables

You will want to pay careful attention to your spaces, but the main concept is putting the text you want to generate in single quotes, and then using + signs around the name of the columns you are querying. Your result should look like this:

grant select on UNUSED_DBA_Maint_DBCC_CheckDB_Error_Results to someuser;
 

For those of you who use Oracle, you can do the same thing there–just using pipes instead of plus signs.

select ‘grant select on ‘||table_name||’ to someuser;’ from dba_tables where owner=’SYS’;
 

 

About jdanton1
A DBA, cyclist, cook (who likes to play chef occasionally)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: