70세 코딩

-- Setup the linked server.
EXEC sp_addlinkedserver  
        @server='ORACLE',
        @srvproduct='Oracle',
        @provider='OraOLEDB.Oracle', 
        @datasrc='ORACLE10';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='ORACLE',
    @useself='false', 
    @locallogin=null, 
    @rmtuser='scott', 
    @rmtpassword='tiger';
 
EXEC sp_serveroption 'ORACLE', 'rpc out', true;
GO
 
-- Execute several statements on the linked Oracle server.
EXEC ( 'SELECT * FROM scott.emp') AT ORACLE;
GO
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', 7902) AT ORACLE;
GO
DECLARE @v INT; 
SET @v = 7902;
EXEC ( 'SELECT * FROM scott.emp WHERE MGR = ?', @v) AT ORACLE;
GO

USE AdventureWorks2008R2;
GO
DECLARE tables_cursor CURSOR
   FOR
   SELECT s.name, t.name 
   FROM sys.objects AS t
   JOIN sys.schemas AS s ON s.schema_id = t.schema_id
   WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
   EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
   FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO