March 15, 2006 at 6:18 am
Hi - I'm in the process of converting a load of T-SQL to remove cursors in order to improve performance. In Oracle, the following example shows one way of processing a results set without the need to use a cursor. Is there an equivalent method using T-SQL?
BEGIN
FOR X IN (
SELECT TABLE_NAME FROM USER_TABLES
)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || X.TABLE_NAME || ' TO myuser';
END LOOP;
END;
/
Thanks
March 15, 2006 at 6:41 am
No SQL Server equivalent but you could use
DECLARE @sql varchar(8000)
SET @sql = ''
SELECT @sql = @sql + 'GRANT SELECT,INSERT,UPDATE,DELETE ON [' + TABLE_NAME + '] TO myuser;'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
EXEC(@sql)
but beware of the 8K limit on varchar, or use this
SELECT 'GRANT SELECT,INSERT,UPDATE,DELETE ON [' + TABLE_NAME + '] TO myuser;'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'dtproperties'
and copy and paste to a QA window and execute
Far away is close at hand in the images of elsewhere.
Anon.
March 15, 2006 at 6:47 am
You can do this:
declare @ierror int, @rcount int, @i int, @table_name varchar(50), @cmd nvarchar(1000)
declare @table table (rownum int identity(1,1), table_name varchar(50))
insert into @table (table_name) select name from sysobjects where xtype = 'u' order by name
set @rcount = @@rowcount
set @i = 1
while (@i <= @rcount)
begin
select @table_name = table_name from @table where rownum = @i
set @cmd = N'grant select, insert, update, delete on '+@table_name+' to public'
exec sp_executesql @cmd
set @ierror = @@error
--do your error handling here
set @i = @i + 1
end
March 16, 2006 at 12:36 pm
You should also be able to do this:
sp_MSforeachtable 'grant select, insert, update, delete on ? to public'
It still uses a cursor, but at least you don't have to.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply