批量修改数据库的结构者

CREATE TABLE #result
    (
   id INT IDENTITY(1, 1) ,
      objectid INT ,
      scemaid INT ,
      NAME VARCHAR(300)
    )
INSERT  INTO #result
        ( objectid ,
          scemaid ,
          NAME
        )
        SELECT  object_id ,
                schema_id ,
                name
        FROM    sys.objects
        WHERE   schema_id = 6
                AND type = 'P'

CREATE TABLE #result2
    (
      id INT IDENTITY(1, 1) ,
      objectid INT ,
      scemaid INT ,
      NAME VARCHAR(300)
    )

INSERT  INTO #result2
        ( objectid ,
          scemaid ,
          NAME
        )
        SELECT  sys.objects.object_id ,
                sys.objects.schema_id ,
                sys.objects.name
        FROM    sys.objects
                INNER JOIN #result ON #result.name = sys.objects.name
                                      AND sys.objects.schema_id = 1
                                      AND sys.objects.type = 'P'
                                     
DECLARE @tempid INT,@MaxId INT
SET @tempid=1

SELECT @MaxId=MAX(id) FROM #result2

WHILE @tempid<=@MaxId
BEGIN
DECLARE @spname VARCHAR(300)
SELECT @spname=NAME FROM #result2 WHERE id=@tempid
IF @spname IS NOT NULL
BEGIN
SELECT @spname='DROP PROC dbo.'+''+@spname+''
EXEC (''+@spname+'')
END
SET  @tempid=@tempid+1
END

SET @tempid=1

SELECT @MaxId=MAX(id) FROM #result

WHILE @tempid<=@MaxId
BEGIN
DECLARE @spname1 VARCHAR(300)
SELECT @spname1=NAME FROM #result WHERE id=@tempid
IF @spname1 IS NOT NULL
BEGIN
SELECT @spname1='ALTER SCHEMA dbo TRANSFER [dyoga36].'+''+@spname1+''
EXEC (''+@spname1+'')
END
SET  @tempid=@tempid+1
END

--ALTER SCHEMA dbo TRANSFER [dyoga36].UpdateSendPointWithPatch
--UPDATE sys.objects
--SET sys.objects.schema_id=1
--FROM sys.objects
--INNER JOIN #result ON #result.objectid = sys.objects.object_id

--exec Changename 'dyoga36','dbo' --将cwa1034所有者更改为dbo

 

转载于:https://www.cnblogs.com/scottpei/archive/2012/09/04/2670041.html