Too many tables in Sybase, how too cope

Question

I have Sybase version 10.0.1.3960 and 'inherited' very large database. I am trying to grant SELECT privileges on all tables to a single user.

The problem is there are a lot of tables, 681 tables to be exact!

Too many tables to write the query manually, so I generated the sql progamatically. The trouble is that the programatically generated is too long. Sybase has a VARCHAR limit of 32767 (2^15-1) and the query is longer than that. I tried defining a LONG VARCHAR but I keep getting an error.

I'm at my rope's end, how can I make this work?

DECLARE @login   VARCHAR(255)
DECLARE @payload VARCHAR(32765) -- <-- too few characters, too many tables
SELECT @login = 'myUserLogin'
SELECT @payload = Result.Payload FROM (
  SELECT LIST(tbl.ApplicableAsset, ';\n') AS 'Payload'
  From (
     SELECT 'GRANT SELECT ON ' + name + ' TO ' + @login +'' AS 'ApplicableAsset'
     FROM sysobjects
     WHERE type='U'
  ) AS tbl
) AS Result
EXECUTE ( @payload )

Write it as a loop resetting @payload for each table. I'm NOt a big fan of cursors; but this might just be time to use it... or write out each select to a temp table and then select from it and iterate though each row.. lots of options here. — xQbert, Apr 25 '14 at 19:55
I'd prefer the looping ... just because it's quiet straight forward ;) — frlan, Apr 25 '14 at 19:56
@MichaelGardner I tried `text` which seemed to impose the same limit (contrary to the manual). I tried `long varchar` getting a syntax error but not `long nvarchar`. Is the difference significant? — recursion.ninja, Apr 28 '14 at 20:24
Docs say it's also 2G, but supports Unicode. IDK if it helps at all. Alternatively, I usually do this in two steps. Step 1 creates a script with the grants, and step 2 executes the script, but I'm in a *nix environment with Sybase ASE - so it's a bit different. — Mike Gardner, Apr 29 '14 at 03:15

score 0 · Accepted Answer · answered Nov 28 '14 at 08:13

You could create a cursor for this case. In cursot you could generate a executed string with control length of string. For example, if Length of string more than 2000 sym - you could execute this string and start to generate a new string.

For example, it could be as:

Decalre Cur Cursor for select One_Grant_Expression from .... 
go   
Open Cur   
Declare @SQLText varchar(8000)   
Declare @OneText varchar(1000)   
fetch Cur into @OneTExt   

while (@@sqlstatus = 0) do  
begin
     Set @SQLText = @SQLText + @OneText

if Length(@SQLText) > 2000

  begin 
       exec (@SQLText)
       Set @SQLText = ''
  end
  fetch Cur into @OneTExt   
end

 If Length(@SQLText) > 0  begin   exec (@SQLText) end

 Close Cur 
go

Too many tables in Sybase, how too cope

1 Answers1