컴퓨토피아

USE sample
GO

-- 시스템 테이블을 직접 수정할 수 있도록 설정
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO

-- 함수는 master 데이타베이스 생성, 소유자는 system_function_schema 지정
USE master
GO

CREATE FUNCTION system_function_schema.fn_greatest ( @x bigint, @y bigint )
 RETURNS bigint
AS
BEGIN
 RETURN ( CASE WHEN @x>@y THEN @x ELSE @y END )
END
GO

CREATE FUNCTION system_function_schema.fn_least( @x bigint, @y bigint )
 RETURNS bigint
AS
BEGIN
 RETURN ( CASE WHEN @x<@y THEN @x ELSE @y END )
END
GO

-- 시스템 테이블을 직접 수정할 수 없도록 0으로 변경(반드시 수행)
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO

-- 생성한 시스템 함수는 모든 데이타베이스에서 호출 가능
USE sample
GO

SELECT fn_greatest(989, 998), fn_least(989, 998)
GO

결과
998 989

/* 시스템 함수 조회하기 */
USE master
GO

SELECT name , OBJECTPROPERTY(id, 'IsScalarFunction'),  *
FROM sysobjects
WHERE uid = USER_ID('system_function_schema')
AND (OBJECTPROPERTY(id, 'IsScalarFunction')=1
 OR OBJECTPROPERTY(id, 'IsTableFunction')=1
 OR OBJECTPROPERTY(id, 'IsInlineFunction')=1 )
GO

/*
 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