_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › るジDB企Synonym襷り鍵
SET NOCOUNT ON
SET STATISTICS IO OFF

CREATE TABLE #Temp(ExecSQL nvarchar(3500));
CREATE TABLE #Rs(RsSQL nvarchar(3500));
DECLARE 
	@DBName nvarchar(255)
,	@SQL nvarchar(3500);

SET @DBName = 'AdventureWorks';
SET @SQL = '
	SELECT 
		''CREATE SYNONYM ['' + TABLE_SCHEMA + ''_'' +	TABLE_NAME + ''] FOR ['' + 
		TABLE_CATALOG + ''].['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']''
	FROM ' + @DBName + '.INFORMATION_SCHEMA.TABLES
	WHERE TABLE_SCHEMA <> ''dbo''';
INSERT #Temp EXEC(@SQL);

DECLARE Cur CURSOR FOR
	SELECT ExecSQL FROM #Temp

OPEN cur;
FETCH NEXT FROM cur INTO @SQL;
WHILE @@FETCH_STATUS NOT IN (-1, -2)
BEGIN
	--EXEC(@SQL);
	--PRINT @SQL;
	INSERT #Rs(RsSQL) VALUES(@SQL);
	FETCH NEXT FROM cur INTO @SQL;
END

SELECT * FROM #Rs

DROP TABLE #Temp
DROP TABLE #Rs
CLOSE cur;
DEALLOCATE cur;
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2018-04-13 23:12:53

螻糾鍵 螳. 蠏碁 伎螻 蠏 譴 蟾.