SQL: multiple table activities

I experienced the needing to do repetitive tasks on many tables such as drop tables, grant permissions, change owner... The best way I found was using cursor.

Example1:
Grant permission on all the tables with table name starting with 'AAA' to a user login containing a '-' sign


USE MyDatabaseName
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
select name from sysobjects where xtype = 'U' and UPPER(name) LIKE 'AAA%'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('GRANT SELECT, INSERT, UPDATE, DELETE ON ' + @TableName + ' TO [User-1]')
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor



Example2:
Drop all the tables with table name starting with 'AAA'


USE MyDatabaseName
DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
select name from sysobjects where xtype = 'U' and UPPER(name) LIKE 'AAA%'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('DROP TABLE ' + @TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Tags:

About author

Vittorio Pavesi