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

Share on Google Plus

About Vittorio Pavesi