[펌] MSSQL 모든 Table Schema를 구하는 스크립트

DB 서버를 관리할 때, 주기적인 데이터 풀백업 말고도 DB 테이블 변경 사항이 발생될 때마다 전체 테이블 스키마를 백업해야한다. 테이블 스키마를 덤프뜰때, GUI 툴인 Enterprise Manager의 "Generate SQL Script" 기능을 사용할 수도 있겠지만, DB 서버마다 그리고 데이터베이스마다 일일히 마우스 클릭한다는건 귀찮은 작업이다. 그리고 마우스 클릭 작업은 관리자가 인지 못하는 실수를 만들 위험도 높다.

EM에서 할 수 있는 작업이라면, 쿼리로도 가능하기에, 프로필로러를 돌린 상태에서 해당 작업을 실행하여 EM이 어떤 쿼리를 실행시키나 살펴봤더니, 여러개의 쿼리가 실행되는데 그 과정을 하나의 스크립트로 작성하기엔 정보가 부족했고, 지금은 퇴사하신 이전 DBA가 썼다는 스크립트도 있는데.. 그게 워낙 대하소설이라.. 차라리 그걸 참고해서 새로 짜는게 맘 편할 것 같았다.

DB 서버와 관리자 컴퓨터가 AD로 묶이지 않았다는 가정 아래, 로컬에서는 윈도우 인증으로, 원격에서는 SQL 서버 인증으로 실행될 수 있도록 짰다. 'config data' 부분만 환경에 맞게 변경해주면 된다. 스크립트의 자세한 내용을 알고 싶은 분은 맨위 주석문의 레퍼런스를 참고하시라.

-- 

-- programmer    ADDNULL

-- latest        2006/11/14

-- description    generate table create script

-- reference    http://support.microsoft.com/kb/152801/ko

--        http://msdn2.microsoft.com/ko-kr/library/ms187693.aspx

--        http://dbforums.com/showthread.php?t=362835

--

DECLARE

    @ServerAddr        VARCHAR(255),

    @ServerID        VARCHAR(255),

    @ServerPassword    VARCHAR(255),

    @DatabaseName        VARCHAR(255),

    @OutputFileName        VARCHAR(255)

DECLARE

    @ObjectServer    INT,

    @ObjectTransfer    INT,

    @ObjectDB    INT,

    @ObjectItem    INT

DECLARE

    @SQLDMOScript_Default            INT,

    @SQLDMOScript_ObjectPermissions    INT,

    @SQLDMOScript_Indexes            INT,

    @ScriptOption                INT

-- config data

SET @ServerAddr = '.'    -- server IP address, port (ex : '127.0.0.1,1433')

SET @ServerID = null    -- user's ID for remote sql authentication (ex : 'sa')

SET @ServerPassword = null    -- user's password for remote sql authentication

SET @DatabaseName = null    -- database name to generate script

SET @OutputFileName = 'c:\table.sql'    -- output file name

-- connect to server

EXEC sp_OACreate 'SQLDMO.SQLServer', @ObjectServer OUTPUT

EXEC sp_OACreate 'SQLDMO.Transfer', @ObjectTransfer OUTPUT

IF (@ServerID IS NULL OR @ServerPassword IS NULL) BEGIN

    -- local (windows authentication)

    EXEC sp_OASetProperty @ObjectServer, 'LoginSecure', 'True'

    EXEC sp_OAMethod @ObjectServer, 'Connect', null, @ServerAddr

END ELSE BEGIN

    -- remote (sql authentication)

    EXEC sp_OAMethod @ObjectServer, 'Connect', null, @ServerAddr, @ServerID, @serverPassword

END

-- get a database object

EXEC sp_OAGetProperty @ObjectServer, 'Databases', @ObjectDB OUTPUT

EXEC sp_OAMethod @ObjectDB, 'Item', @ObjectItem OUTPUT, @DatabaseName

-- set object property

SET @SQLDMOScript_Default = 4    

SET @SQLDMOScript_ObjectPermissions = 2  

SET @SQLDMOScript_Indexes = 73736

SET @ScriptOption = @SQLDMOScript_Default | @SQLDMOScript_ObjectPermissions | @SQLDMOScript_Indexes

EXEC sp_OASetProperty @ObjectTransfer, 'CopyAllTables', 1

EXEC sp_OASetProperty @ObjectTransfer, 'ScriptType', @ScriptOption

-- output

EXEC sp_OAMethod @ObjectItem, 'ScriptTransfer', NULL, @ObjectTransfer, 2, @OutputFileName

-- destory objects

EXEC sp_OADestroy @ObjectItem

EXEC sp_OADestroy @ObjectDB

EXEC sp_OADestroy @ObjectTransfer

EXEC sp_OADestroy @ObjectServer
[출처] [펌] MSSQL 모든 Table Schema를 구하는 스크립트|작성자 주디