Posted by a_dba_used_to_oracle on 09/25/82 11:59

I give up - will someone please tell me why in _Oracle_ I can do
something like:


select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';

and get:

CREATE PUBLIC SYNONYM CHAINED_ROWS FOR SYSADM.CHAINED_ROWS;
CREATE PUBLIC SYNONYM CH_CAP FOR SYSADM.CH_CAP;
CREATE PUBLIC SYNONYM PSACTIVITYDEFN FOR SYSADM.PSACTIVITYDEFN;
CREATE PUBLIC SYNONYM PSACTIVITYDEL FOR SYSADM.PSACTIVITYDEL;

etc, etc, etc, for ALL tables owned by SYSADM

However, in SQL Server, I simply CANNOT write:

select 'grant select, insert, update, delete on '+table_name+ ' to
psselect'
from INFORMATION_SCHEMA.TABLES
where table_name like '%PS%'

and have it write out to a SQL script file that can be executed!!!!

I have tried something like the following:

create procedure count_ps_tables @pscount int OUTPUT
AS
select table_name from INFORMATION_SCHEMA.TABLES
where table_name like '%PS%'
set @pscount=@@ROWCOUNT
return(0)
exec count_ps_tables @t_count OUTPUT
begin
while @t_count !=0
declare @grant_stmt varchar(133)
select @grant_stmt ='grant select on '+table_name+ ' to PSSELECT'
from INFORMATION_SCHEMA.TABLES
where table_name like '%PS%'
order by table_name
print @grant_stmt
exec (@grant_stmt)
set @t_count = @t_count - 1
end

but it _does not work_

If anyone can tell me - I would be ever so grateful!

• Next in forum: Re: this is very, very, very frustrating!!!!!!
• Prev in forum: Re: Q on joining tables with nullable fields
• Thread view: this is very, very, very frustrating!!!!!!

[Reply to this message]