컴퓨토피아

/*
 SQL 서버 메타데이터 정보 가져오는 방법

  1. 시스템 저장 프로시져 사용 방법(권고, 메타 데이터를 가져오기 위해서는 시스템 저장 프로시져, 시스템 함수, 시스템 제공 뷰
  2. INFORMATION 스키마 뷰 사용 방법
*/

SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

/*
결과 정보 반환
Northwind dbo Customers CustomerID 1 NULL No  nchar 5 10 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers CompanyName 2 NULL No  nvarchar 40 80 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers ContactName 3 NULL YES nvarchar 30 60 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers ContactTitle 4 NULL YES nvarchar 30 60 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers Address 5 NULL YES nvarchar 60 120 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers City 6 NULL YES nvarchar 15 30 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers Region 7 NULL YES nvarchar 15 30 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers PostalCode 8 NULL YES nvarchar 10 20 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers Country 9 NULL YES nvarchar 15 30 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers Phone 10 NULL YES nvarchar 24 48 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
Northwind dbo Customers Fax 11 NULL YES nvarchar 24 48 NULL NULL NULL NULL NULL NULL Unicode NULL NULL Korean_Wansung_CI_AS NULL NULL NULL
*/

SQL SERVER 2000에서 제공하는 INFORMATION 스키마 뷰

CHECK_CONSTRAINTS
COLUMNS
DOMAINS
......

-- 기본으로 제공하는 INFORMATION 스키마 뷰 활용
USE pubs
GO

SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
GO

-- 사용자가 INFORMATION 스키마 뷰 생성
USE master
GO

EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_MS_upd_sysobj_category 1
GO

CREATE VIEW INFORMATION_SCHEMA.DEFAULT_CONSTRAINS
AS

SELECT db_name() as CONSTRAINT_CATALOG,
 user_name(c_obj.uid) AS CONSTRAINT_SCHEMA,
 c_obj.name AS CONSTRAINT_NAME,  
 com.text as DEFAULT_CLAUSE
FROM sysobjects c_obj, syscomments com
WHERE  c_obj.uid = user_id()
 AND c_obj.id = com.id
 AND c_obj.xtype = 'D'
GO

EXEC sp_MS_upd_sysobj_category 2
GO

EXEC sp_configure 'allow updates', 0
GO

RECONFIGURE WITH OVERRIDE
GO

USE sample
GO

SELECT *
FROM INFORMATION_SCHEMA.DEFAULT_CONSTRAINS
GO

SELECT db_name(), user_name()
GO

SELECT *
FROM sysobjects
WHERE uid = user_id() AND xtype = 'D'
GO

SELECT *
FROM syscomments

USE master
GO

-- 계정 생성
EXEC sp_addlogin 'DevUser', 'ad1234', 'sample'
GO

USE sample
GO

-- 사용자 추가
EXEC sp_adduser 'DevUser', 'DevUser', 'db_datareader'
GO

--역할 기능 추가
EXEC sp_addrolemember 'db_datawriter', 'DevUser'
GO

2. NT 로그인 계정을 sample 데이타베이스에 사용자로 등록, 데이타베이스 내의 테이블들을 읽는 권한을 설정하는 예제

EXEC sp_grantlogin 'Admin\winadmin'
GO

EXEC sp_defaultdb 'Admin\winadmin', 'sample'
GO

USE sample
GO

EXEC sp_grantdbaccess 'Admin\winadmin', 'winadmin'
GO

EXEC sp_addrolemember 'db_datareader', 'winadmin'
GO

3. SQL Server 로그인 이름

// 새로그인 생성, ID:BackupAdmin, PASSWORD : 1234, DB: Sample
EXEC sp_addlogin 'BackupAdmin', '1234', 'sample'
GO

// 사용자 이름, 새 사용자의 이름,  역할 OR 그룹
EXEC sp_adduser 'BackupAdmin', 'BackupAdmin', 'db_backupoperator'
GO

// 로그인 삭제
EXEC sp_droprolemember 'db_backupoperator', 'BackupAdmin'
GO

// 현재 데이터베이스에서 보안 계정을 제거합니다. 계정에 종속되는 사용 권한과 별칭은 자동으로 제거
EXEC sp_revokedbaccess 'BackupAdmin'
GO

// 로그인 계정 삭제
EXEC sp_droplogin 'BackupAdmin'
GO

3. 기존 로그인과 사용자에 관한 정보 확인

// 데이타베이스에 사용하고 있는 DB
EXEC sp_helplogins
GO

USE sample
GO

// 현재 DB에 생성되어 있는 사용자 계정을 얻어온다.
EXEC sp_helpuser
GO

// 암호변경하기
EXEC sp_password 'ad1234', 'ad5678', 'DevUser'
GO

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를 구하는 스크립트|작성자 주디