'SQL'에 해당되는 글 6건
- 2009.05.18 Running Stored Procedures with ASP.NET
- 2009.03.04 MS Sql Server Backup Stored Procedure
- 2009.03.04 Backup databases stored procedure
- 2009.02.13 SQL Server Table 명세서 출력 SP
- 2009.02.13 Microsoft SQL Server 2005 System Diagram View
- 2009.01.31 저장 프로시저 목록 뽑기..
2009. 5. 18. 09:18
Running Stored Procedures with ASP.NET
2009. 5. 18. 09:18 in SQL
출처 : http://www.dotnetjunkies.ddj.com/Article/9AE62C44-3841-4687-B906-2F6D4A5A4622.dcik
원 소스는 위 링크에서 퍼 왔는데, 지금은 사라졌음.
최초 VB로 된 소스였는데.. C#으로 포팅함.
1. DB 접속해서 SP 목록 가져오고,
2. SP 선택해서 파라미터 가져오고,
3. Input Value 넣고 실행하면,
4. 결과를 알려줌.
RunStoredProcedures2.aspx : VB 소스
RunStoredProcedures.aspx : C# 소스
2009. 3. 4. 17:38
MS Sql Server Backup Stored Procedure
2009. 3. 4. 17:38 in 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
2009. 3. 4. 15:34
Backup databases stored procedure
2009. 3. 4. 15:34 in SQL
출처 : 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
2009. 2. 13. 10:18
SQL Server Table 명세서 출력 SP
2009. 2. 13. 10:18 in SQL
출처 : sqler.pe.kr 에서 가져온 것 같음.
-- 2011년 3월 8일
-- 1. 디자인 깨지던 것 수정함.
-- 2. 테이블 설명 추가함.
-- p.s 예전부터 알고 있었는데 귀찮아서 안하다가 이제사 고침. 3년만에 고쳤네...
기본은 그대로 가져다 쓰고 약간 수정한 사항이 있고,
이후에는 html 등에서 사용가능하게 하면 좋을 것 같고,
엑셀변환도 클릭하면 되게끔 하면 좋을 것 같고,
sql 2000 시절에는 어떤 분이 만들어 두신 것 같은데...
sql 2005는 잘 없네요.
그래도 이게 어디냐...
SP도 아래와 같이 관리되면 좋을 텐데...
쩝....
p.s 2009년 5월 8일 추가 수정함.
-- =============================================
-- SQL Database documentation script
-- Description: T-SQL script to generate the database document for SQL server 2000/2005
-- 기능 정의 : Database의 Table, View 에 대한 전체 명세서 출력
-- 수정일 : 2009년 2월 13일
-- 수정자 : 김민국
-- 수정내역 : 인쇄 시 편하게 보기 위해서 테이블 간에 page-break 추가.
-- 수정내역 : 전체 출력과 테이블명 검색 기능 구분 추가
-- 수정내역 : 단순 스크립트를 SP 형식으로 변경
-- 수정일 : 2009년 5월 8일
-- 수정자 : 김민국
-- 수정내역 : 상단 정렬 오류 수정
--
-- 실행 예 : exec table_desc '', ''
-- 실행 예 : exec table_desc '1', 'TB_KM_'
-- Drop PROCEDURE dbo.table_desc
-- =============================================
CREATE PROCEDURE [dbo].[table_desc]
@gubun varchar(1) -- '' : 전체 출력, '1' : 테이블명 검색 후 출력
,@TableNm varchar(50) -- 검색할 테이블명
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
--Declare @tmpOutput varchar(max)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server 2000')
set @SqlVersion = '2000'
else
set @SqlVersion = '2005'
Print '<html>'
Print '<head>'
Print '<title>::' + DB_name() + '::</title>'
Print '<style>'
Print ' body {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' td {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' }'
Print ' th {'
Print ' font-family:verdana;'
Print ' font-size:9pt;'
Print ' background:#d3d3d3;'
Print ' }'
Print ' table'
Print ' {'
Print ' background:#d3d3d3;'
Print ' }'
Print ' tr'
Print ' {'
Print ' background:#ffffff;'
Print ' }'
Print ' </style>'
Print '</head>'
Print '<body>'
Print '<table border="0" cellspacing="0" cellpadding="0" width="750px">'
Print '<colgroup><col width="100%" /></colgroup>'
Print '<tr>'
Print '<td scope="col">'
set nocount on
if @SqlVersion = '2000'
begin
if @gubun = '' -- 전체 출력
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
order by table_type, table_schema, table_name
end
else -- 테이블 검색
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
where table_name like @TableNm +'%'
order by table_type, table_schema, table_name
end
end
else if @SqlVersion = '2005'
begin
if @gubun = '' -- 전체 출력
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
order by type, s.name, o.name
end
else -- 테이블 검색
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
and o.name like @TableNm + '%'
order by type, s.name, o.name
end
end
Set @maxi = @@rowcount
set @i = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px" align="left"><colgroup><col width="100%" /></colgroup><tr><td scope="col" style="height:50;font-size:14pt;text-align:center;"><a name="index"></a><b>Index</b></td></tr></table><br /><br /><br /><br />'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px" align="left"><colgroup><col width="5%" /><col width="15%" /><col width="65%" /><col width="15%" /></colgroup><tr><th scope="col">Sr</th><th scope="col">Object</th><th scope="col">Description</th><th scope="col">Type</th></tr>'
While(@i <= @maxi)
begin
select @Output = '<tr><td align="center">' + Cast((@i) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + [description] + '</td><td>' + Type + '</td></tr>'
from #Tables where id = @i
print @Output
set @i = @i + 1
end
print '</table><br />'
print '<div style=''page-break-before: always;''><!--[if IE 7]><br style=''height:0; line-height:0''><![endif]--></div>'
Print '</td>'
Print '</tr>'
Print '</table>'
Print '<table border="0" cellspacing="0" cellpadding="0" width="750px">'
Print '<colgroup><col width="100%" /></colgroup>'
Print '<tr>'
Print '<td scope="col">'
set @i = 1
While(@i <= @maxi)
begin
--table header
select @Output = '<tr><th scope="col" align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>', @description = [description]
from #Tables where id = @i
print '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col" align="right"><a href="#index">Index</a></td></tr>'
print @Output
print '</table><br />'
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Description</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />'
--table columns
truncate table #Columns
if @SqlVersion = '2000'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2000
Select c.name,
type_name(xtype) + (
case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
then '(' + cast(length as varchar) + ')'
when type_name(xtype) = 'decimal'
then '(' + cast(prec as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when isnullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(8000))
from syscolumns c
inner join #Tables t on t.object_id = c.id
left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description'
where t.id = @i
order by c.colorder
end
else if @SqlVersion = '2005'
begin
insert into #Columns (Name, Type, Nullable, [description])
--FOR 2005
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when is_nullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description'
where t.id = @i
order by c.column_id
end
Set @maxj = @@rowcount
set @j = 1
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col" ><b>Table Columns</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="5%" /><col width="30%" /></colgroup><tr><th scope="col" >Sr.</th><th scope="col" >Name</th><th scope="c
ol" >Datatype</th><th scope="col" >Nullable</th><th scope="col" >Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + upper(isnull(Type,'')) + '</td><td scope="col" align="center">' + isnull(Nullable,'N') + '</td>
<td scope="col">' + isnull([description],'') + '</td></tr>'
from #Columns where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
--reference key
truncate table #FK
if @SqlVersion = '2000'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2000
select object_name(constid), s.name, object_name(rkeyid) , s1.name
from sysforeignkeys f
inner join sysobjects o on o.id = f.constid
inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
inner join #Tables t on t.object_id = f.fkeyid
where t.id = @i
order by 1
end
else if @SqlVersion = '2005'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2005
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)
from sys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i
order by f.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Refrence Keys</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Reference To</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">[' + isnull(refObj,'N') + '].[' + isnull(refCol,'
N') + ']</td></tr>'
from #FK where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Default Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and
convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '10101000'
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), c.definition
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Default Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Value</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Check Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and ( convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00101000'
or convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00100100')
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), definition
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Check Constraints</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Column</th><t
h scope="col">Definition</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col">' + isnull(col,'') + '</td><td scope="col">' + isnull(definition,'') + '</td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Triggers
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name)
select tr.name
FROM sysobjects tr
inner join #Tables t on t.object_id = tr.parent_obj
where t.id = @i and tr.type = 'TR'
order by tr.name
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i
order by tr.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Triggers</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="45%" /><col width="50%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Description</th></tr>'
While(@j <= @maxj)
begin
select @Output = '<tr><td scope="col" align="center">' + Cast((@j) as varchar) + '</td><td scope="col">' + isnull(name,'') + '</td><td scope="col"></td></tr>'
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end
print '</table><br />'
end
--Indexes
truncate table #Indexes
if @SqlVersion = '2000'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name
from sysindexes i
inner join sysindexkeys k on k.indid = i.indid and k.id = i.id
inner join syscolumns c on c.id = k.id and c.colorder = k.colid
inner join #Tables t on t.object_id = i.id
where t.id = @i and i.name not like '_WA%'
order by i.name, i.keycnt
end
else if @SqlVersion = '2005'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end, col_name(i.object_id, c.column_id)
from sys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
inner join #Tables t on t.object_id = i.object_id
where t.id = @i
order by i.name, c.column_id
end
Set @maxj = @@rowcount
set @j = 1
set @sr = 1
if (@maxj >0)
begin
print '<table border="0" cellspacing="0" cellpadding="0" width="750px"><colgroup><col width="100%" /></colgroup><tr><td scope="col"><b>Indexes</b></td></tr></table>'
print '<table border="0" cellspacing="1" cellpadding="0" width="750px"><colgroup><col width="5%" /><col width="30%" /><col width="30%" /><col width="35%" /></colgroup><tr><th scope="col">Sr.</th><th scope="col">Name</th><th scope="col">Type</th><th
scope="col">Columns</th></tr>'
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes where id = @j
if @last <> @current and @last <> ''
begin
print '<tr><td scope="col" align="center">' + Cast((@sr) as varchar) + '</td><td scope="col">' + @last + '</td><td scope="col">' + @typ + '</td><td scope="col">' + @Output + '</td></tr>'
set @Output = ''
set @sr = @sr + 1
end
select @Output = @Output + cols + '<br />' , @typ = type
from #Indexes where id = @j
set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print '<tr><td scope="col" align="center">' + Cast((@sr) as varchar) + '</td><td scope="col">' + @last + '</td><td scope="col">' + @typ + '</td><td scope="col">' + @Output + '</td></tr>'
end
print '</table><br />'
end
if @i < @maxi -- 마지막 페이지는 page-break 안함
begin
print '<div style=''page-break-before: always;''><!--[if IE 7]><br style=''height:0; line-height:0''><![endif]--></div>'
end
Set @i = @i + 1;
--Print @Output
end
Print '</td>'
Print '</tr>'
Print '</table>'
Print '</body>'
Print '</html>'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
set nocount off
END
2009. 2. 13. 10:13
Microsoft SQL Server 2005 System Diagram View
2009. 2. 13. 10:13 in SQL
2009. 1. 31. 11:52
저장 프로시저 목록 뽑기..
2009. 1. 31. 11:52 in SQL
저장 프로시저 목록 뽑기..
select
'프로시저명' = o.name
, '파라미터명' = p.name
, 'Type' = type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
)
from sys.sysobjects o left outer join sys.parameters p
on o.id = p.object_id
where xtype in ('x','p') order by o.name, p.parameter_id