Iteratively Apply DDL with Sybase

Overview

This is a small code snippet that will iterate through a set of tables and apply some common operation. Useful for applying grants or performing some universal action like dropping and recreating tables.

I only posted this here because I keep forgetting the syntax, seem to keep using this repeatedly and Sybase Transact SQL isn't very forgiving or friendly.

Sample

 1 CREATE TABLE #table_list ( name VARCHAR(40) )
 2 go
 3 
 4 INSERT INTO #table_list 
 5 SELECT name FROM sysobjects WHERE name IN (
 6     'table1', 'table2', 'table3')
 7 
 8 DECLARE @current VARCHAR(40)
 9 
10 WHILE (SELECT COUNT(*) FROM #table_list) > 0
11 BEGIN
12     SELECT @current = name FROM #table_list
13 
14     EXECUTE ('GRANT SELECT ON ' + @current + ' TO public')
15 
16     DELETE FROM #table_list WHERE name = @current
17 END
18 
19 GO
20 

You still need to list out the tables manually which is a pain; a general SELECT FROM sysobjects won't get you anywhere as other system tables are pulled in too as well as triggers, stored procedures etc.

This is one example where ANSI SQL needs to have some decent administrative features added to it; this kind of operation must be pretty common in the real world yet the syntax is a total pig as we are bending SQL into procedural form.