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
- Blogger Comment
- Facebook Comment
Subscribe to:
Post Comments
(
Atom
)
0 commenti:
Post a Comment