'sql'에 해당되는 글 4건
- 2009.03.04 MS Sql Server Backup Stored Procedure
- 2009.03.04 Backup databases stored procedure
- 2009.02.13 Microsoft SQL Server 2005 System Diagram View
- 2009.02.06 MS sql - 함수 모음
출처 : http://www.sqlteam.com
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup
--
-- AUTHOR : Tara Kizer
--
-- 수정일 : 2009년 3월 4일
-- 수정자 : 김민국
-- 수정 내역 : Microsoft Sql Server 2008 지원, compression 옵션 추가
--
-- INPUTS : @path - 백업 파일 위치, null 인 경우에는 Default Backup Directory에 백업 파일 생성
-- @dbType - 백업할 데이터베이스 유형 or '-' 옵션 시에는 백업할 데이터베이스명
-- All, System, User, or dash followed by database name (ex. -Toolbox)
-- @bkpType - 백업 타입
-- Full, TLog, Diff
-- @compression - 압축 여부, Enterprise, Developer Edition 에서만 사용 가능
-- N, Y
-- @retention - 백업 파일 유지 기간(days), -1 옵션은 제한 없음.
-- @liteSpeed - perform backup using LiteSpeed (Imceda product)
-- N, Y
--
-- OUTPUTS : None
--
-- RETURN CODES : 0-10 (see @error table variable at the end for the messages)
--
-- DEPENDENCIES : None
--
-- DESCRIPTION : Performs backups.
--
-- EXAMPLES (optional) : EXEC isp_Backup @path = 'C:\MSSQL\Backup\', @dbType = '-acct_bar', @bkpType = 'Full', @compression='N', @retention = 5, @liteSpeed = 'N'
--
-- Drop Proc : Drop proc [dbo].[isp_Backup]
----------------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_Backup]
(@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @compression char(1) = 'N', @retention smallint = 2, @liteSpeed char(1) = 'N')
AS
SET NOCOUNT ON
DECLARE @now char(14) -- current date in the form of yyyymmddhhmmss
DECLARE @dbName sysname -- database name that is currently being processed
DECLARE @cmd nvarchar(4000) -- dynamically created DOS command
DECLARE @result int -- result of the dir DOS command
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @fileName varchar(200) -- path and file name of the BAK file
DECLARE @edition int -- edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise)
DECLARE @rc int -- return code
DECLARE @extension char(4) -- extension for backup file
DECLARE @version varchar(2) -- one or two digit version number, i.e. 8 (2000) or 9 (2005) or 10 (2008)
-- log shipping tables have been renamed in 2005
SET @version = CONVERT(varchar(2), SERVERPROPERTY('ProductVersion'))
IF @version NOT IN ('8', '9', '10')
BEGIN
SET @rc = 1
GOTO EXIT_ROUTINE
END
-- Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not
SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
-- Developer or Enterprise Edition이 아닌 경우에는 @compression을 'N'으로 설정
IF @compression ='Y' AND @edition <> 3
BEGIN
--SET @compression = 'N'
SET @rc = 12
GOTO EXIT_ROUTINE
END
-- validate input parameters
IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%'
BEGIN
SET @rc = 2
GOTO EXIT_ROUTINE
END
IF @dbType LIKE '-%' AND @version = '8'
BEGIN
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END
ELSE IF @dbType LIKE '-%' AND ( @version = '9' or @version = '10' )
BEGIN
IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END
IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff')
BEGIN
SET @rc = 4
GOTO EXIT_ROUTINE
END
IF @dbType = 'System' AND @bkpType <> 'Full'
BEGIN
SET @rc = 5
GOTO EXIT_ROUTINE
END
IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')
BEGIN
SET @rc = 6
GOTO EXIT_ROUTINE
END
-- use the default backup directory if @path is null
IF @path IS NULL
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'
-- we need the backslash after the path, so add it if it wasn't provided in the input parameter
IF RIGHT(@path, 1) <> '\'
SET @path = @path + '\'
CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)
-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
BEGIN
IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
BEGIN
SET @rc = 7
GOTO EXIT_ROUTINE
END
IF @edition = 3
BEGIN
IF @version = '8'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
ELSE IF @version = '9' or @version = '10'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
END
IF @version = '9' or @version ='10'
BEGIN
IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
BEGIN
SET @rc = 11
GOTO EXIT_ROUTINE
END
END
INSERT INTO #WhichDatabase(dbName)
VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
END
ELSE IF @dbType = 'All'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND ( @version = '9' or @version ='10' )
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE -- version is 9
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
IF @version = 8
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND ( @version = '9' or @version = '10' )
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
SET @rc = 9
GOTO EXIT_ROUTINE
END
-- Remove snapshots
IF @version = '9' or @version = '10'
DELETE t
FROM #WhichDatabase t
INNER JOIN master.sys.databases d
ON t.dbName = d.[name]
WHERE d.source_database_id IS NOT NULL
-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @rowCnt <> 0
BEGIN
IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
PRINT 'Skipping transaction log backup of ' + @dbName
ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')
PRINT 'Skipping differential backup of ' + @dbName
ELSE
BEGIN
-- Build the dir command that will check to see if the directory exists
SET @cmd = 'dir ' + @path + @dbName
-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN
-- Build the mkdir command
SET @cmd = 'mkdir ' + @path + @dbName
-- Create the directory
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
IF @@ERROR <> 0
BEGIN
SET @rc = 10
GOTO EXIT_ROUTINE
END
END
-- The directory exists, so let's delete files older than two days
ELSE IF @retention <> -1
BEGIN
-- Stores the name of the file to be deleted
DECLARE @whichFile VARCHAR(1000)
CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))
-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'
-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master..xp_cmdshell @cmd
-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @rowCnt = @@ROWCOUNT
-- Interate through the temp table until there are no more files to delete
WHILE @rowCnt <> 0
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @path + + @dbName + '\' + @whichFile + ' /Q /F'
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @whichFile
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @rowCnt = @@ROWCOUNT
END
DROP TABLE #DeleteOldFiles
END
-- Get the current date using style 120, remove all dashes, spaces, and colons
SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
SET @extension =
CASE
WHEN @bkpType = 'Full' THEN '.BAK'
WHEN @bkpType = 'TLog' THEN '.TRN'
ELSE '.DIF'
END
-- Build the backup path and file name, backup the database
IF @liteSpeed = 'N'
BEGIN
SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension
IF @compression = 'N'
BEGIN
IF @bkpType = 'FULL'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT
ELSE IF @bkpType = 'DIFF'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, DIFFERENTIAL
ELSE
BACKUP LOG @dbName
TO DISK = @filename
WITH INIT
END
ELSE --@compression = 'Y'
BEGIN
IF @bkpType = 'FULL'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, COMPRESSION
ELSE IF @bkpType = 'DIFF'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, DIFFERENTIAL, COMPRESSION
ELSE
BACKUP LOG @dbName
TO DISK = @filename
WITH INIT
END
END
ELSE
BEGIN
SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension
DECLARE @regOutput varchar(20) -- stores the output from the registry
DECLARE @numProcs INT -- stores the number of processors that the server has registered
-- Get the number of processors that the server has
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @regOutput OUTPUT
-- We want n - 1 threads, where n is the number of processors
SET @numProcs = CONVERT(int, @regOutput) - 1
IF @bkpType = 'FULL'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1
ELSE IF @bkpType = 'DIFF'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1,
@with = 'DIFFERENTIAL'
ELSE
EXEC master.dbo.xp_backup_log
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1
END
END
-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE FROM #WhichDatabase
WHERE dbName = @dbName
-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase
SET @rowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'
END
SET @rc = 0
EXIT_ROUTINE:
IF @rc <> 0
BEGIN
DECLARE @rm varchar(500)
DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))
INSERT INTO @error(returnCode, returnMessage)
SELECT 0, 'Success' UNION ALL
SELECT 1, 'Version is not 2000 or 2005' UNION ALL
SELECT 2, 'Invalid option passed to @dbType' UNION ALL
SELECT 3, 'Database passed to @dbType does not exist' UNION ALL
SELECT 4, 'Invalid option passed to @bkpType' UNION ALL
SELECT 5, 'Only full backups are allowed on system databases' UNION ALL
SELECT 6, 'Invalid option passed to @liteSpeed' UNION ALL
SELECT 7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL
SELECT 8, 'Will not backup the tlog on a log shipped database' UNION ALL
SELECT 9, 'No databases to be backed up' UNION ALL
SELECT 10, 'Unable to create directory' UNION ALL
SELECT 11, 'Can not backup database snapshots' UNION ALL
SELECT 12, 'Developer 또는 Enterprise Edition이 아닌 경우에는 compression 옵션을 사용할 수 없습니다.'
SELECT @rm = returnMessage
FROM @error
WHERE returnCode = @rc
RAISERROR(@rm, 16, 1)
END
RETURN @rc
출처 : http://weblogs.sqlteam.com/tarad/archive/2008/05/21/60606.aspx
Backup databases stored procedure - new version
I have updated my backup stored procedure, isp_Backup, as the old version had two issues. It did not exclude database snapshots, which can't be backed up. It also was not checking for the number of CPUs in the registry properly. The CPU check only affects backups when SQL Litespeed is used.
In the next version, I will exclude those databases that are being log shipped by SQL Litespeed as we don't want to interfere with the LSNs. I am already checking for this condition for native backups. I will also add code to perform a full backup when it is performing a differential or transaction log backup and a full backup does not exist. This will be done to avoid backup errors.
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : isp_Backup
--
-- AUTHOR : Tara Kizer
--
-- 수정일 : 2009년 3월 4일
-- 수정자 : 김민국
-- 수정 내역 : sql server 2008 지원, compression 옵션 추가
--
-- INPUTS : @path - 백업 파일 위치, null 인 경우에는 Default Backup Directory에 백업 파일 생성
-- @dbType - 백업할 데이터베이스 유형 or '-' 옵션 시에는 백업할 데이터베이스명
-- All, System, User, or dash followed by database name (ex. -Toolbox)
-- @bkpType - 백업 타입
-- Full, TLog, Diff
-- @compression - 압축 여부, Enterprise, Developer Edition 에서만 사용 가능
-- N, Y
-- @retention - 백업 파일 유지 기간(days), -1 옵션은 제한 없음.
-- @liteSpeed - perform backup using LiteSpeed (Imceda product)
-- N, Y
--
-- OUTPUTS : None
--
-- RETURN CODES : 0-10 (see @error table variable at the end for the messages)
--
-- DEPENDENCIES : None
--
-- DESCRIPTION : Performs backups.
--
-- EXAMPLES (optional) : EXEC isp_Backup @path = 'C:\MSSQL\Backup\', @dbType = '-acct_bar', @bkpType = 'Full', @compression='N', @retention = 5, @liteSpeed = 'N'
--
-- Drop Proc : Drop proc [dbo].[isp_Backup]
----------------------------------------------------------------------------------------------------
CREATE PROC [dbo].[isp_Backup]
(@path varchar(100), @dbType sysname = 'All', @bkpType char(4) = 'Full', @compression char(1) = 'N', @retention smallint = 2, @liteSpeed char(1) = 'N')
AS
SET NOCOUNT ON
DECLARE @now char(14) -- current date in the form of yyyymmddhhmmss
DECLARE @dbName sysname -- database name that is currently being processed
DECLARE @cmd nvarchar(4000) -- dynamically created DOS command
DECLARE @result int -- result of the dir DOS command
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @fileName varchar(200) -- path and file name of the BAK file
DECLARE @edition int -- edition of SQL Server (1 - Personal or Desktop Engine; 2 - Standard; 3 - Developer or Enterprise)
DECLARE @rc int -- return code
DECLARE @extension char(4) -- extension for backup file
DECLARE @version varchar(2) -- one or two digit version number, i.e. 8 (2000) or 9 (2005) or 10 (2008)
-- log shipping tables have been renamed in 2005
SET @version = CONVERT(varchar(2), SERVERPROPERTY('ProductVersion'))
IF @version NOT IN ('8', '9', '10')
BEGIN
SET @rc = 1
GOTO EXIT_ROUTINE
END
-- Enterprise and Developer editions have msdb.dbo.log_shipping* tables, other editions do not
SET @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
-- Developer or Enterprise Edition이 아닌 경우에는 @compression을 'N'으로 설정
IF @compression ='Y' AND @edition <> 3
BEGIN
--SET @compression = 'N'
SET @rc = 12
GOTO EXIT_ROUTINE
END
-- validate input parameters
IF @dbType IS NOT NULL AND @dbType NOT IN ('All', 'System', 'User') AND @dbType NOT LIKE '-%'
BEGIN
SET @rc = 2
GOTO EXIT_ROUTINE
END
IF @dbType LIKE '-%' AND @version = '8'
BEGIN
IF NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END
ELSE IF @dbType LIKE '-%' AND ( @version = '9' or @version = '10' )
BEGIN
IF NOT EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 3
GOTO EXIT_ROUTINE
END
END
IF @bkpType IS NOT NULL AND @bkpType NOT IN ('Full', 'TLog', 'Diff')
BEGIN
SET @rc = 4
GOTO EXIT_ROUTINE
END
IF @dbType = 'System' AND @bkpType <> 'Full'
BEGIN
SET @rc = 5
GOTO EXIT_ROUTINE
END
IF @liteSpeed IS NOT NULL AND @liteSpeed NOT IN ('N', 'Y')
BEGIN
SET @rc = 6
GOTO EXIT_ROUTINE
END
-- use the default backup directory if @path is null
IF @path IS NULL
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output, 'no_output'
-- we need the backslash after the path, so add it if it wasn't provided in the input parameter
IF RIGHT(@path, 1) <> '\'
SET @path = @path + '\'
CREATE TABLE #WhichDatabase(dbName SYSNAME NOT NULL)
-- put the databases to be backed up into temp table
IF @dbType LIKE '-%'
BEGIN
IF @bkpType = 'TLog' AND DATABASEPROPERTYEX(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)), 'RECOVERY') = 'SIMPLE'
BEGIN
SET @rc = 7
GOTO EXIT_ROUTINE
END
IF @edition = 3
BEGIN
IF @version = '8'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_databases WHERE database_name = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
ELSE IF @version = '9' or @version = '10'
BEGIN
IF EXISTS (SELECT * FROM msdb.dbo.log_shipping_primary_databases WHERE primary_database = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
BEGIN
SET @rc = 8
GOTO EXIT_ROUTINE
END
END
END
IF @version = '9' or @version ='10'
BEGIN
IF EXISTS (SELECT * FROM master.sys.databases WHERE [name] = SUBSTRING(@dbType, 2, DATALENGTH(@dbType)) AND source_database_id IS NOT NULL)
BEGIN
SET @rc = 11
GOTO EXIT_ROUTINE
END
END
INSERT INTO #WhichDatabase(dbName)
VALUES(SUBSTRING(@dbType, 2, DATALENGTH(@dbType)))
END
ELSE IF @dbType = 'All'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND ( @version = '9' or @version ='10' )
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE -- version is 9
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE IF @dbType = 'System'
BEGIN
IF @version = 8
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE [name] IN ('master', 'model', 'msdb')
ORDER BY [name]
END
ELSE IF @dbType = 'User'
BEGIN
IF @edition = 3 AND @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT database_name FROM msdb.dbo.log_shipping_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @edition = 3 AND ( @version = '9' or @version = '10' )
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
[name] NOT IN (SELECT primary_database FROM msdb.dbo.log_shipping_primary_databases) AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE IF @version = '8'
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
ELSE
INSERT INTO #WhichDatabase (dbName)
SELECT [name]
FROM master.sys.databases
WHERE
[name] NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServerTempDB') AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
ORDER BY [name]
END
ELSE -- no databases to be backed up
BEGIN
SET @rc = 9
GOTO EXIT_ROUTINE
END
-- Remove snapshots
IF @version = '9' or @version = '10'
DELETE t
FROM #WhichDatabase t
INNER JOIN master.sys.databases d
ON t.dbName = d.[name]
WHERE d.source_database_id IS NOT NULL
-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @rowCnt <> 0
BEGIN
IF @bkpType = 'TLog' AND @dbType IN ('All', 'User') AND DATABASEPROPERTYEX(@dbName, 'RECOVERY') = 'SIMPLE'
PRINT 'Skipping transaction log backup of ' + @dbName
ELSE IF @bkpType = 'Diff' AND @dbName IN ('master', 'model', 'msdb')
PRINT 'Skipping differential backup of ' + @dbName
ELSE
BEGIN
-- Build the dir command that will check to see if the directory exists
SET @cmd = 'dir ' + @path + @dbName
-- Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master..xp_cmdshell @cmd, NO_OUTPUT
-- If the directory does not exist, we must create it
IF @result <> 0
BEGIN
-- Build the mkdir command
SET @cmd = 'mkdir ' + @path + @dbName
-- Create the directory
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
IF @@ERROR <> 0
BEGIN
SET @rc = 10
GOTO EXIT_ROUTINE
END
END
-- The directory exists, so let's delete files older than two days
ELSE IF @retention <> -1
BEGIN
-- Stores the name of the file to be deleted
DECLARE @whichFile VARCHAR(1000)
CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))
-- Build the command that will list out all of the files in a directory
SELECT @cmd = 'dir ' + @path + @dbName + ' /OD'
-- Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master..xp_cmdshell @cmd
-- Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE '%<DIR>%' OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() - @retention
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @rowCnt = @@ROWCOUNT
-- Interate through the temp table until there are no more files to delete
WHILE @rowCnt <> 0
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @path + + @dbName + '\' + @whichFile + ' /Q /F'
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
-- To move to the next file, the current file name needs to be deleted from the temp table
DELETE FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @whichFile
-- Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles
SET @rowCnt = @@ROWCOUNT
END
DROP TABLE #DeleteOldFiles
END
-- Get the current date using style 120, remove all dashes, spaces, and colons
SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
SET @extension =
CASE
WHEN @bkpType = 'Full' THEN '.BAK'
WHEN @bkpType = 'TLog' THEN '.TRN'
ELSE '.DIF'
END
-- Build the backup path and file name, backup the database
IF @liteSpeed = 'N'
BEGIN
SET @fileName = @path + @dbName + '\' + @dbName + '_' + @now + @extension
IF @compression = 'N'
BEGIN
IF @bkpType = 'FULL'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT
ELSE IF @bkpType = 'DIFF'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, DIFFERENTIAL
ELSE
BACKUP LOG @dbName
TO DISK = @filename
WITH INIT
END
ELSE --@compression = 'Y'
BEGIN
IF @bkpType = 'FULL'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, COMPRESSION
ELSE IF @bkpType = 'DIFF'
BACKUP DATABASE @dbName
TO DISK = @filename
WITH INIT, DIFFERENTIAL, COMPRESSION
ELSE
BACKUP LOG @dbName
TO DISK = @filename
WITH INIT
END
END
ELSE
BEGIN
SET @fileName = @path + @dbName + '\' + @dbName + '_LS_' + @now + @extension
DECLARE @regOutput varchar(20) -- stores the output from the registry
DECLARE @numProcs INT -- stores the number of processors that the server has registered
-- Get the number of processors that the server has
EXEC master..xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SYSTEM\CurrentControlSet\Control\Session Manager\Environment\',
@value_name = 'NUMBER_OF_PROCESSORS',
@value = @regOutput OUTPUT
-- We want n - 1 threads, where n is the number of processors
SET @numProcs = CONVERT(int, @regOutput) - 1
IF @bkpType = 'FULL'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1
ELSE IF @bkpType = 'DIFF'
EXEC master.dbo.xp_backup_database
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1,
@with = 'DIFFERENTIAL'
ELSE
EXEC master.dbo.xp_backup_log
@database = @dbName,
@filename = @fileName,
@threads = @numProcs,
@init = 1
END
END
-- To move onto the next database, the current database name needs to be deleted from the temp table
DELETE FROM #WhichDatabase
WHERE dbName = @dbName
-- Get the database to be backed up
SELECT TOP 1 @dbName = dbName
FROM #WhichDatabase
SET @rowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY '00:00:05'
END
SET @rc = 0
EXIT_ROUTINE:
IF @rc <> 0
BEGIN
DECLARE @rm varchar(500)
DECLARE @error table (returnCode int PRIMARY KEY CLUSTERED, returnMessage varchar(500))
INSERT INTO @error(returnCode, returnMessage)
SELECT 0, 'Success' UNION ALL
SELECT 1, 'Version is not 2000 or 2005' UNION ALL
SELECT 2, 'Invalid option passed to @dbType' UNION ALL
SELECT 3, 'Database passed to @dbType does not exist' UNION ALL
SELECT 4, 'Invalid option passed to @bkpType' UNION ALL
SELECT 5, 'Only full backups are allowed on system databases' UNION ALL
SELECT 6, 'Invalid option passed to @liteSpeed' UNION ALL
SELECT 7, 'Can not backup tlog when using SIMPLE recovery model' UNION ALL
SELECT 8, 'Will not backup the tlog on a log shipped database' UNION ALL
SELECT 9, 'No databases to be backed up' UNION ALL
SELECT 10, 'Unable to create directory' UNION ALL
SELECT 11, 'Can not backup database snapshots' UNION ALL
SELECT 12, 'Developer 또는 Enterprise Edition이 아닌 경우에는 compression 옵션을 사용할 수 없습니다.'
SELECT @rm = returnMessage
FROM @error
WHERE returnCode = @rc
RAISERROR(@rm, 16, 1)
END
RETURN @rc
SQL2005_Sys_Views.pdf- 집계 합수
집계 함수는 어디에 사용되는가?
집계 함수는 값 집합에 대한 계산을 수행하고 단일 값을 반환한다.
COUNT를 제외한 집계 함수는 Null 값을 무시한다.
집계 함수는 SELECT 문의 GROUP BY 절과 함께 사용하는 경우가 많다.
집계 함수는 어디에 사용되는가?
집계 함수는 값 집합에 대한 계산을 수행하고 단일 값을 반환한다.
COUNT를 제외한 집계 함수는 Null 값을 무시한다.
집계 함수는 SELECT 문의 GROUP BY 절과 함께 사용하는 경우가 많다.
집계 함수는 다음 경우에서만 식으로 사용할 수 있다.
SELECT 문의 선택 목록(하위 쿼리 또는 외부 쿼리)
COMPUTE 또는 COMPUTE BY 절
HAVING 절
SELECT 문의 선택 목록(하위 쿼리 또는 외부 쿼리)
COMPUTE 또는 COMPUTE BY 절
HAVING 절
AVG
그룹에서 값의 평균을 반환한다. Null 값은 무시된다.
BINARY_CHECKSUM
테이블의 행 또는 식의 목록에 대해 계산된 이진 검사값을 반환한다.
BINARY_CHECKSUM을 사용하여 테이블에 행에 대한 변경 사항을 찾을 수 있다.
CHECKSUM
테이블의 행 또는 식의 목록에 대해 계산한 검사값을 반환한다.
CHECKSUM 값은 해시 인덱스를 작성하는 데 사용하기 위한 것이다.
CHECKSUM_AGG
그룹에서 값의 검사값을 반환한다.
Null 값은 무시된다.
그룹에서 값의 검사값을 반환한다.
Null 값은 무시된다.
COUNT
그룹에 포함된 항목 개수를 반환한다.
그룹에 포함된 항목 개수를 반환한다.
COUNT_BIG
그룹에 포함된 항목 개수를 반환한다.
COUNT_BIG은 COUNT 함수와 비슷하며 반환값만 다르다.
COUNT_BIG은 bigint 데이터 형식 값을 반환하고 COUNT는 int 데이터 형식 값을 반환한다.
그룹에 포함된 항목 개수를 반환한다.
COUNT_BIG은 COUNT 함수와 비슷하며 반환값만 다르다.
COUNT_BIG은 bigint 데이터 형식 값을 반환하고 COUNT는 int 데이터 형식 값을 반환한다.
GROUPING
행이 CUBE 또는 ROLLUP 연산자를 통해 추가될 때 추가 열의 출력이 1이 되거나 행이 CUBE 또는 ROLLUP의 결과가 아닐 때 추가 열의 출력이 0이 되도록 하는 집계 함수이다.
그룹화는 CUBE 또는 ROLLUP 연산자가 들어 있는 GROUP BY 절과 관련된 선택 목록에서만 사용될 수 있다.
행이 CUBE 또는 ROLLUP 연산자를 통해 추가될 때 추가 열의 출력이 1이 되거나 행이 CUBE 또는 ROLLUP의 결과가 아닐 때 추가 열의 출력이 0이 되도록 하는 집계 함수이다.
그룹화는 CUBE 또는 ROLLUP 연산자가 들어 있는 GROUP BY 절과 관련된 선택 목록에서만 사용될 수 있다.
MAX
식의 최대값을 반환한다.
식의 최대값을 반환한다.
MIN
식에서 최소값을 반환한다.
식에서 최소값을 반환한다.
SUM
식의 모든 값의 합계 또는 DISTINCT 값만 반환한다.
SUM과 함께 사용할 수 있는 것은 숫자 열이다. Null 값은 무시된다.
SUM과 함께 사용할 수 있는 것은 숫자 열이다. Null 값은 무시된다.
STDEV
특정 식의 모든 값에 대한 통계적 표준 편차를 반환한다.
특정 식의 모든 값에 대한 통계적 표준 편차를 반환한다.
STDEVP
특정 식의 모든 값의 모집단에 대한 통계적 표준 편차를 반환한다.
특정 식의 모든 값의 모집단에 대한 통계적 표준 편차를 반환한다.
VAR
지정한 식에 있는 모든 값의 통계적 분산을 반환한다.
지정한 식에 있는 모든 값의 통계적 분산을 반환한다.
VARP
지정한 식에 있는 모든 값의 모집단에 대한 통계적 분산을 반환한다.
지정한 식에 있는 모든 값의 모집단에 대한 통계적 분산을 반환한다.
- 수치 연산 함수
수치 연산 함수는 우리가 쉽게 이해하는 수치 연산의 결과값을 반환한다.
일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 값을 반환한다.
수치 연산 함수는 우리가 쉽게 이해하는 수치 연산의 결과값을 반환한다.
일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 값을 반환한다.
ABS, CEILING, DEGREES, FLOOR, POWER, RADIANS, SIGN 등의 산술 함수는 입력 값과 동일한
데이터 형식을 갖는 값을 반환한다.
EXP, LOG, LOG10, SQUARE, SQRT를 포함하여 삼각 함수와 그 밖의 함수는 입력 값을 float로 캐스트하고 float 값을 반환한다.
RAND를 제외한 모든 수치 연산 함수는 입력 값과 동일한 형식으로 반환하는 확정적인 함수이다.
EXP, LOG, LOG10, SQUARE, SQRT를 포함하여 삼각 함수와 그 밖의 함수는 입력 값을 float로 캐스트하고 float 값을 반환한다.
RAND를 제외한 모든 수치 연산 함수는 입력 값과 동일한 형식으로 반환하는 확정적인 함수이다.
ABS
지정한 수식의 절대값을 양수로 반환한다.
지정한 수식의 절대값을 양수로 반환한다.
ACOS
코사인 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크코사인이라고도 한다.
코사인 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크코사인이라고도 한다.
ASIN
사인 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크사인이라고도 한다
ATAN
탄젠트 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크탄젠트라고도 한다.
사인 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크사인이라고도 한다
ATAN
탄젠트 값이 지정한 float 식인 각도를 라디안으로 반환한다.
아크탄젠트라고도 한다.
ATN2
탄젠트 값이 지정한 두 개의 float 식 사이에 속하는 각도를 라디안으로 반환한다.
아크탄젠트라고도 한다.
탄젠트 값이 지정한 두 개의 float 식 사이에 속하는 각도를 라디안으로 반환한다.
아크탄젠트라고도 한다.
CEILING
지정한 숫자 식 이상인 최소 정수를 반환한다.
지정한 숫자 식 이상인 최소 정수를 반환한다.
COS
지정한 식에서 지정한 각도의 삼각법 코사인을 라디안 단위로 반환한다.
지정한 식에서 지정한 각도의 삼각법 코사인을 라디안 단위로 반환한다.
COT
지정한 float 식에서 지정한 각도의 삼각법 코탄젠트를 라디안 단위로 반환한다.
지정한 float 식에서 지정한 각도의 삼각법 코탄젠트를 라디안 단위로 반환한다.
DEGREES
지정된 라디안 단위의 각도에 대해 해당하는 각도를 도 단위로 반환합니다.
지정된 라디안 단위의 각도에 대해 해당하는 각도를 도 단위로 반환합니다.
EXP
특정 float 식의 지수 값을 반환한다.
특정 float 식의 지수 값을 반환한다.
FLOOR
주어진 숫자 식 이하인 최대 정수를 반환한다.
주어진 숫자 식 이하인 최대 정수를 반환한다.
LOG
주어진 float 식의 자연 로그를 반환한다.
주어진 float 식의 자연 로그를 반환한다.
LOG10
주어진 float 식의 기수 10 로그를 반환한다.
주어진 float 식의 기수 10 로그를 반환한다.
PI
PI의 상수 값을 반환한다.
PI의 상수 값을 반환한다.
POWER
주어진 식의 값을 지정된 거듭제곱으로 반환한다.
주어진 식의 값을 지정된 거듭제곱으로 반환한다.
RADIANS
숫자 식을 도 단위로 입력하면 라디안으로 반환한다.
숫자 식을 도 단위로 입력하면 라디안으로 반환한다.
RAND
0부터 1까지의 임의 float 값을 반환한다.
ROUND
특정 길이나 정밀도로 반올림한 숫자 식을 반환한다.
특정 길이나 정밀도로 반올림한 숫자 식을 반환한다.
SIGN
주어진 식의 양수(+1), 영(0) 또는 음수(-1) 기호를 반환한다.
주어진 식의 양수(+1), 영(0) 또는 음수(-1) 기호를 반환한다.
SIN
특정 각도(라디안)의 삼각 사인을 근사 숫자(float) 식으로 반환한다.
특정 각도(라디안)의 삼각 사인을 근사 숫자(float) 식으로 반환한다.
SQUARE
특정 식의 제곱을 반환한다.
특정 식의 제곱을 반환한다.
SQRT
특정 식의 제곱근을 반환한다.
특정 식의 제곱근을 반환한다.
TAN
입력한 식의 탄젠트를 반환한다.
입력한 식의 탄젠트를 반환한다.
- 날짜 및 시간 함수
날짜 및 시간 함수는 다음과 같은 특징이 있다
날짜 및 시간 함수는 우리가 쉽게 이해하는 날짜 및 시간의 결과값을 반환한다.
일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 문자열, 숫자 또는 날짜 및 시간 값을 반환한다.
DATEADD
지정한 날짜에 시간 간격을 더하여 새 datetime 값을 반환한다.
지정한 날짜에 시간 간격을 더하여 새 datetime 값을 반환한다.
DATEDIFF
지정한 두 날짜 간에 교차되는 날짜와 시간 경계값을 반환한다.
지정한 두 날짜 간에 교차되는 날짜와 시간 경계값을 반환한다.
DATENAME
지정한 날짜의 특정 날짜 부분을 나타내는 문자열을 반환한다.
지정한 날짜의 특정 날짜 부분을 나타내는 문자열을 반환한다.
DATEPART
지정한 날짜의 특정 날짜 부분을 나타내는 정수를 반환한다.
DAY
지정한 날짜의 일 부분을 나타내는 정수를 반환한다.
지정한 날짜의 일 부분을 나타내는 정수를 반환한다.
GETDATE
현재 시스템 날짜와 시간을 datetime 값의 Microsoft SQL Server 표준 내부 형식으로 반환한다.
현재 시스템 날짜와 시간을 datetime 값의 Microsoft SQL Server 표준 내부 형식으로 반환한다.
GETUTCDATE
국제 표준시 또는 그리니치 표준시를 나타내는 datetime 값을 반환한다. 현재 UTC 시간은 해당 지역의 현재 시간과 SQL Server가 실행 중인 컴퓨터 운영 체제의 표준 시간대를 기준으로 반환한다.
국제 표준시 또는 그리니치 표준시를 나타내는 datetime 값을 반환한다. 현재 UTC 시간은 해당 지역의 현재 시간과 SQL Server가 실행 중인 컴퓨터 운영 체제의 표준 시간대를 기준으로 반환한다.
MONTH
지정된 날짜의 월 부분을 나타내는 정수를 반환한다.
지정된 날짜의 월 부분을 나타내는 정수를 반환한다.
YEAR
지정한 날짜의 연도 부분을 표시하는 정수를 반환한다.
지정한 날짜의 연도 부분을 표시하는 정수를 반환한다.
- 문자열 함수
문자열 함수는 다음과 같은 특징이 있다
문자열 함수는 우리가 쉽게 이해하는 문자열의 결과값을 반환한다.
일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 숫자 또는 문자열 값을 반환한다.
일반적인 인수에 의해 제공되는 값을 기반으로 계산을 수행하고 숫자 또는 문자열 값을 반환한다.
ASCII
문자 식에서 가장 왼쪽 문자의ASCII 코드 값을 반환한다.
문자 식에서 가장 왼쪽 문자의ASCII 코드 값을 반환한다.
CHAR
int ASCII 코드를 문자로 변환한다.
int ASCII 코드를 문자로 변환한다.
CHARINDEX
문자열에서 지정한 식의 시작 위치를 반환한다.
문자열에서 지정한 식의 시작 위치를 반환한다.
DIFFERENCE
두 문자 식에서 SOUNDEX 값의 차이를 정수로 반환한다.
두 문자 식에서 SOUNDEX 값의 차이를 정수로 반환한다.
LEFT
왼쪽에서부터 지정된 문자 수에서 시작하는 문자열의 일부를 반환한다.
왼쪽에서부터 지정된 문자 수에서 시작하는 문자열의 일부를 반환한다.
LEN
주어진 문자열 식의 바이트 수 대신 후행 공백을 제외한 문자 수를 반환한다.
주어진 문자열 식의 바이트 수 대신 후행 공백을 제외한 문자 수를 반환한다.
LOWER
대문자로 된 데이터를 소문자로 변환한 후 문자 식을 반환한다.
대문자로 된 데이터를 소문자로 변환한 후 문자 식을 반환한다.
LTRIM
선행 공백을 제거한 후 문자 식을 반환한다.
선행 공백을 제거한 후 문자 식을 반환한다.
NCHAR
유니코드 표준에서 정의된 대로 주어진 정수 코드로 유니코드 문자를 반환한다.
유니코드 표준에서 정의된 대로 주어진 정수 코드로 유니코드 문자를 반환한다.
PATINDEX
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환하거나 패턴을 찾지 못하는 경우에는 0을 반환한다.
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환하거나 패턴을 찾지 못하는 경우에는 0을 반환한다.
REPLACE
첫째 문자열 식에서 둘째 문자열 식의 모든 인스턴스를 찾아 셋째 식으로 반환한다.
첫째 문자열 식에서 둘째 문자열 식의 모든 인스턴스를 찾아 셋째 식으로 반환한다.
QUOTENAME
입력 문자열이 유효한 기호와 함께 유니코드 문자열을 반환한다.
입력 문자열이 유효한 기호와 함께 유니코드 문자열을 반환한다.
REPLICATE
지정한 횟수만큼 문자 식을 반복한다.
지정한 횟수만큼 문자 식을 반복한다.
REVERSE
문자 식을 반대로 반환한다.
문자 식을 반대로 반환한다.
RIGHT
오른쪽부터 지정한 수만큼의 문자를 제외한 나머지 문자열 일부를 반환한다.
오른쪽부터 지정한 수만큼의 문자를 제외한 나머지 문자열 일부를 반환한다.
RTRIM
모든 후행 공백을 제거한 다음 문자열을 반환한다.
모든 후행 공백을 제거한 다음 문자열을 반환한다.
SOUNDEX
두 문자열의 유사성을 평가하기 위한 네 글자(SOUNDEX) 코드를 반환한다.
두 문자열의 유사성을 평가하기 위한 네 글자(SOUNDEX) 코드를 반환한다.
SPACE
반복된 공백의 문자열을 반환한다.
반복된 공백의 문자열을 반환한다.
STR
숫자 데이터에서 변환된 문자 데이터를 반환한다.
숫자 데이터에서 변환된 문자 데이터를 반환한다.
STUFF
지정한 시작 지점에서 지정한 문자 길이를 삭제한 다음 다른 문자 집합을 삽입한다.
지정한 시작 지점에서 지정한 문자 길이를 삭제한 다음 다른 문자 집합을 삽입한다.
SUBSTRING
문자, 이진, 텍스트, 이미지 식의 일부를 반환한다.
문자, 이진, 텍스트, 이미지 식의 일부를 반환한다.
UNICODE
유니코드 표준에서 정의한 대로 입력 식에 있는 첫 글자의 정수 값을 반환한다.
유니코드 표준에서 정의한 대로 입력 식에 있는 첫 글자의 정수 값을 반환한다.
UPPER
문자 식을 대문자로 변환된 소문자 데이터로 반환한다.
문자 식을 대문자로 변환된 소문자 데이터로 반환한다.
- 구성 함수
구성 함수는 다음과 같은 특징이 있다
구성 함수는 현재 구성된 서버의 옵션 설정에 대한 값들을 반환한다.
모든 구성 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
@@ATEFIRST
매주 지정된 첫째 날을 나타내는 SET DATEFIRST 매개 변수의 현재 값을 반환한다.
1은 월요일, 2는 화요일 순으로 하여 7은 일요일이다.
매주 지정된 첫째 날을 나타내는 SET DATEFIRST 매개 변수의 현재 값을 반환한다.
1은 월요일, 2는 화요일 순으로 하여 7은 일요일이다.
@@DBTS
현재 데이터베이스에 대해 현재 timestamp 데이터 형식의 값을 반환한다.
현재 데이터베이스에 대해 현재 timestamp 데이터 형식의 값을 반환한다.
@@LANGID
현재 사용 중인 언어의 로컬 언어 식별자(ID)를 반환한다.
현재 사용 중인 언어의 로컬 언어 식별자(ID)를 반환한다.
@@LANGUAGE
현재 사용 중인 언어의 이름을 반환한다.
현재 사용 중인 언어의 이름을 반환한다.
@@LOCK_TIMEOUT
현재 세션의 현재 잠금 시간 제한 설정을 밀리초 단위로 반환한다.
현재 세션의 현재 잠금 시간 제한 설정을 밀리초 단위로 반환한다.
@@MAX_CONNECTIONS
SQL Server에서 허용한 최대 동시 사용자 연결 수를 반환한다.
SQL Server에서 허용한 최대 동시 사용자 연결 수를 반환한다.
@@MAX_PRECISION
현재 서버에 설정되어 있는 decimal과 numeric 데이터 형식의 정밀도 수준을 반환한다.
현재 서버에 설정되어 있는 decimal과 numeric 데이터 형식의 정밀도 수준을 반환한다.
@@NESTLEVEL
현재 저장 프로시저 실행의 중첩 수준을 반환한다.
현재 저장 프로시저 실행의 중첩 수준을 반환한다.
@@OPTIONS
현재 SET 옵션에 대한 정보를 반환한다.
현재 SET 옵션에 대한 정보를 반환한다.
@@REMSERVER
원격 SQL Server 데이터베이스 서버의 이름이 로그인 레코드에 나타날 때 이름을 반환한다.
원격 SQL Server 데이터베이스 서버의 이름이 로그인 레코드에 나타날 때 이름을 반환한다.
@@SERVERNAME
SQL Server를 실행하는 동안 로컬 서버의 이름을 반환한다.
SQL Server를 실행하는 동안 로컬 서버의 이름을 반환한다.
@@SERVICENAME
SQL Server가 실행 중인 레지스트리 키의 이름을 반환한다. @@SERVICENAME은 현재 인스턴스가 기본 인스턴스인 경우MSSQLServer를 반환한다. 이 함수는 현재 인스턴스가 명명된 인스턴스이면 인스턴스 이름을 반환한다.
SQL Server가 실행 중인 레지스트리 키의 이름을 반환한다. @@SERVICENAME은 현재 인스턴스가 기본 인스턴스인 경우MSSQLServer를 반환한다. 이 함수는 현재 인스턴스가 명명된 인스턴스이면 인스턴스 이름을 반환한다.
@@SPID
현재 사용자 프로세스의 서버 프로세스 식별자(ID)를 반환한다.
현재 사용자 프로세스의 서버 프로세스 식별자(ID)를 반환한다.
@@TEXTSIZE
SELECT 문이 반환하는 text 또는 image 데이터의 최대 길이를 바이트 단위로 지정하는 SET 문의 현재 TEXTSIZE 옵션 값을 반환한다.
SELECT 문이 반환하는 text 또는 image 데이터의 최대 길이를 바이트 단위로 지정하는 SET 문의 현재 TEXTSIZE 옵션 값을 반환한다.
@@VERSION
현재 설치되어 있는 SQL Server의 날짜, 버전, 프로세서 유형을 반환한다.
현재 설치되어 있는 SQL Server의 날짜, 버전, 프로세서 유형을 반환한다.
- 메타 데이터 함수
메타 데이터 함수는 다음과 같은 특징이 있다
메타 데이터 함수는 데이터베이스와 데이터베이스 개체에 대한 정보를 반환한다.
모든 메타 데이터 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
COL_LENGTH
열의 정의된 길이(바이트)를 반환한다.
열의 정의된 길이(바이트)를 반환한다.
COL_NAME
지정한 테이블 ID와 열 ID에 대한 데이터베이스 열의 이름을 반환한다.
지정한 테이블 ID와 열 ID에 대한 데이터베이스 열의 이름을 반환한다.
COLUMNPROPERTY
열 또는 프로시저 매개 변수에 대한 정보를 반환한다.
열 또는 프로시저 매개 변수에 대한 정보를 반환한다.
DATABASEPROPERTY
주어진 데이터베이스와 속성 이름에 대해 명명된 데이터베이스 속성 값을 반환한다.
주어진 데이터베이스와 속성 이름에 대해 명명된 데이터베이스 속성 값을 반환한다.
DATABASEPROPERTYEX
지정한 데이터베이스에 대해 지정한 데이터베이스 옵션이나 속성의 현재 설정을 반환한다.
지정한 데이터베이스에 대해 지정한 데이터베이스 옵션이나 속성의 현재 설정을 반환한다.
DB_ID
데이터베이스 ID 번호를 반환한다.
데이터베이스 ID 번호를 반환한다.
DB_NAME
데이터베이스 이름을 반환한다.
FILE_ID
현재 데이터베이스의 주어진 논리 파일 이름의 파일 ID 번호를 반환한다.
현재 데이터베이스의 주어진 논리 파일 이름의 파일 ID 번호를 반환한다.
FILE_NAME
주어진 파일 ID에 대한 논리 파일 이름을 반환한다.
주어진 파일 ID에 대한 논리 파일 이름을 반환한다.
FILEGROUP_ID
주어진 파일 그룹 이름의 파일 그룹 ID를 반환한다.
주어진 파일 그룹 이름의 파일 그룹 ID를 반환한다.
FILEGROUP_NAME
주어진 파일 그룹 ID에 대한 파일 그룹 이름을 반환한다.
주어진 파일 그룹 ID에 대한 파일 그룹 이름을 반환한다.
FILEGROUPPROPERTY
파일 그룹과 속성 이름이 주어졌을 때 지정된 파일 그룹 속성 값을 반환한다.
파일 그룹과 속성 이름이 주어졌을 때 지정된 파일 그룹 속성 값을 반환한다.
FILEPROPERTY
파일 이름과 속성 이름이 주어졌을 때 지정된 파일 이름 속성 값을 반환한다.
파일 이름과 속성 이름이 주어졌을 때 지정된 파일 이름 속성 값을 반환한다.
fn_listextendedproperty
데이터베이스 개체의 확장 속성 값을 반환한다.
데이터베이스 개체의 확장 속성 값을 반환한다.
FULLTEXTCATALOGPROPERTY
전체 텍스트 카탈로그 속성에 대한 정보를 반환한다.
FULLTEXTSERVICEPROPERTY
전체 텍스트 서비스 수준 속성에 대한 정보를 반환한다.
전체 텍스트 서비스 수준 속성에 대한 정보를 반환한다.
INDEX_COL
인덱스된 열 이름을 반환한다.
인덱스된 열 이름을 반환한다.
INDEXKEY_PROPERTY
인덱스 키에 대한 정보를 반환한다.
인덱스 키에 대한 정보를 반환한다.
INDEXPROPERTY
주어진 테이블 ID, 인덱스 이름, 속성 이름에 대해 명명된 인덱스 속성 값을 반환한다.
주어진 테이블 ID, 인덱스 이름, 속성 이름에 대해 명명된 인덱스 속성 값을 반환한다.
OBJECT_ID
데이터베이스 개체 ID를 반환한다.
데이터베이스 개체 ID를 반환한다.
OBJECT_NAME
데이터베이스 개체 이름을 반환한다.
데이터베이스 개체 이름을 반환한다.
OBJECTPROPERTY
현재 데이터베이스의 개체에 대한 정보를 반환한다.
현재 데이터베이스의 개체에 대한 정보를 반환한다.
@@PROCID
현재 프로시저의 저장 프로시저 식별자(ID)를 반환한다.
현재 프로시저의 저장 프로시저 식별자(ID)를 반환한다.
SQL_VARIANT_PROPERTY
sql_variant 값에 대한 기본 데이터 형식 및 기타 정보를 반환한다.
sql_variant 값에 대한 기본 데이터 형식 및 기타 정보를 반환한다.
TYPEPROPERTY
데이터 형식에 관한 정보를 반환한다.
데이터 형식에 관한 정보를 반환한다.
- 커서 함수
커서 함수는 다음과 같은 특징이 있다
커서 함수는 커서에 대한 정보를 반환한다.
모든 커서 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
@@CURSOR_ROWS
현재 연결에서 마지막으로 열린 커서에 있는 자격을 갖춘 행의 수를 반환한다.
현재 연결에서 마지막으로 열린 커서에 있는 자격을 갖춘 행의 수를 반환한다.
CURSOR_STATUS
저장 프로시저의 호출자가 프로시저에서 주어진 매개 변수에 대해 커서 및 결과 집합을 반환한다.
저장 프로시저의 호출자가 프로시저에서 주어진 매개 변수에 대해 커서 및 결과 집합을 반환한다.
@@FETCH_STATUS
현재 연결로 열린 모든 커서에 대해 실행된 마지막 FETCH 문의 상태를 반환한다.
현재 연결로 열린 모든 커서에 대해 실행된 마지막 FETCH 문의 상태를 반환한다.
- 행 집합 함수
행 집합 함수는 다음과 같은 특징이 있다
행 집합 함수는 테이블 참조 대신 사용할 수 있는 개체를 반환한다.
모든 행 집합 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
CONTAINSTABLE
문자 기반 데이터 형식이 포함된 열에서 특정 단어나 구와 정확히 일치하거나 비슷하게 일치하는 단어를 검색하거나, 서로 근접한 단어 검색, 가중치 검색에서 일치한 0개 이상의 행이 있는 테이블을 반환한다.
문자 기반 데이터 형식이 포함된 열에서 특정 단어나 구와 정확히 일치하거나 비슷하게 일치하는 단어를 검색하거나, 서로 근접한 단어 검색, 가중치 검색에서 일치한 0개 이상의 행이 있는 테이블을 반환한다.
FREETEXTTABLE
텍스트와 의미는 일치하지만 단어는 정확히 일치하지 않는 값의 문자 기반 데이터 형식이 포함된 열에 대해 행이 없거나 하나 이상인 테이블을 반환한다.
텍스트와 의미는 일치하지만 단어는 정확히 일치하지 않는 값의 문자 기반 데이터 형식이 포함된 열에 대해 행이 없거나 하나 이상인 테이블을 반환한다.
OPENDATASOURCE
연결된 서버 이름을 사용하지 않고 네 부분으로 된 개체 이름의 연결 정보를 제공한다.
연결된 서버 이름을 사용하지 않고 네 부분으로 된 개체 이름의 연결 정보를 제공한다.
OPENQUERY
OLE DB 데이터 원본인 주어진 연결된 서버에 대해 지정된 통과 쿼리를 실행한다. 쿼리는 여러 결과 집합을 반환하지만 OPENQUERY는 첫 번째 것만 반환한다.
OLE DB 데이터 원본인 주어진 연결된 서버에 대해 지정된 통과 쿼리를 실행한다. 쿼리는 여러 결과 집합을 반환하지만 OPENQUERY는 첫 번째 것만 반환한다.
OPENROWSET
OLE DB 데이터 원본에서 원격 데이터를 액세스하는 데 필요한 모든 연결 정보를 제공한다. 쿼리는 여러 결과 집합을 반환하지만 OPENROWSET는 첫 번째 것만 반환한다.
OLE DB 데이터 원본에서 원격 데이터를 액세스하는 데 필요한 모든 연결 정보를 제공한다. 쿼리는 여러 결과 집합을 반환하지만 OPENROWSET는 첫 번째 것만 반환한다.
OPENXML
OPENXML은 XML 문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 표시될 수 있는 Transact-SQL 문에 OPENXML을 사용할 수 있다.
OPENXML은 XML 문서에 대한 행 집합 뷰를 제공한다. OPENXML이 행 집합 공급자이므로 테이블, 뷰 또는 OPENROWSET 함수 등의 행 집합 공급자가 표시될 수 있는 Transact-SQL 문에 OPENXML을 사용할 수 있다.
-보안 함수
보안 함수는 다음과 같은 특징이 있다
보안 함수는 과거 버전에서 저장프로시저가 담당했던 부분을 함수로 만들어 2000에서 제공하는 함수로 사용자와 역할에 관한 정보를 반환한다. 모든 보안 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
fn_trace_geteventinfo
추적한 이벤트에 대한 정보를 반환한다.
추적한 이벤트에 대한 정보를 반환한다.
fn_trace_getfilterinfo
지정한 추적에 적용되는 필터에 대한 정보를 반환한다.
지정한 추적에 적용되는 필터에 대한 정보를 반환한다.
fn_trace_getinfo
지정한 추적이나 기존 추적에 대한 정보를 반환한다.
지정한 추적이나 기존 추적에 대한 정보를 반환한다.
fn_trace_gettable
추적 파일 정보를 테이블 형식으로 반환한다.
추적 파일 정보를 테이블 형식으로 반환한다.
HAS_DBACCESS
사용자가 지정된 데이터베이스를 액세스할 수 있는지에 대한 정보를 반환한다.
사용자가 지정된 데이터베이스를 액세스할 수 있는지에 대한 정보를 반환한다.
IS_MEMBER
현재 사용자가 지정된 Windows Server 그룹 또는 Microsoft SQL Server 역할의 구성원인지 여부를 표시한다.
현재 사용자가 지정된 Windows Server 그룹 또는 Microsoft SQL Server 역할의 구성원인지 여부를 표시한다.
IS_SRVROLEMEMBER
현재 사용자 로그인이 지정된 서버 역할의 구성원인지 여부를 나타낸다.
현재 사용자 로그인이 지정된 서버 역할의 구성원인지 여부를 나타낸다.
SUSER_SID
사용자 로그인 이름에 대한 보안 ID(SID)를 반환한다.
사용자 로그인 이름에 대한 보안 ID(SID)를 반환한다.
SUSER_SNAME
사용자 보안 ID(SID)로부터 로그인 ID 이름을 반환한다.
사용자 보안 ID(SID)로부터 로그인 ID 이름을 반환한다.
USER_ID
사용자의 데이터베이스 ID 번호를 반환한다.
사용자의 데이터베이스 ID 번호를 반환한다.
USER
기본값이 지정되지 않은 경우에 현재 사용자의 데이터베이스 사용자 이름에 대해 시스템이 제공한 값을 테이블에 삽입할 수 있도록 허용한다.
기본값이 지정되지 않은 경우에 현재 사용자의 데이터베이스 사용자 이름에 대해 시스템이 제공한 값을 테이블에 삽입할 수 있도록 허용한다.
- 텍스트 및 이미지 함수
텍스트 및 이미지 함수는 다음과 같은 특징이 있다
텍스트 및 이미지 함수는 텍스트와 이미지 포인터의 위치를 반환한다.
모든 텍스트 및 이미지 함수는 항상 동일한 값을 반환하지 않는 비확정성 함수이다.
ATINDEX
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환한다.
유효한 모든 text 및 character 데이터 형식에서 패턴이 처음으로 지정된 식에서 발생한 시작 위치를 반환한다.
TEXTPTR
varbinary 형식의 text, ntext 또는 image 열에 해당하는 텍스트 포인터 값을 반환한다.
varbinary 형식의 text, ntext 또는 image 열에 해당하는 텍스트 포인터 값을 반환한다.
TEXTVALID
주어진 텍스트 포인터가 유효한지 여부를 반환한다.
주어진 텍스트 포인터가 유효한지 여부를 반환한다.
Prev
Rss Feed