MSSQL DBA가 알아야하는 것들 모음
/************************************************************************************************
** DBA 관리용 시스템 프로시저 생성
drop proc sp_table0
drop proc sp_column0
drop proc sp_block0
drop proc sp_server0
drop proc sp_dboption0
drop proc sp_filegroup0
drop proc sp_lock0
drop proc sp_index0
drop proc sp_filepageno0
drop proc sp_constraint0
drop proc sp_trigger0
drop proc sp_converthex0
drop proc sp_spaceused0
drop proc sp_rowlength0
drop proc sp_freedisksize0
drop proc sp_object0
drop prco sp_showcontig0
************************************************************************************************/
/************************************************************************************************
-- sp_table0
-- 디폴트로 유저 테이블을, 그외 시스템과 뷰를 보여준다.
-- 파라미터로는 'system','view','all'이 있다.
************************************************************************************************/
USE master
GO
IF OBJECT_ID('sp_table0') IS NOT NULL
DROP PROCEDURE sp_table0
GO
CREATE PROC sp_table0
@table_type VARCHAR(100) = NULL
AS
SET NOCOUNT ON
DECLARE @owner SYSNAME
DECLARE @name SYSNAME
DECLARE @oid INT
DECLARE @type CHAR(2)
DECLARE @pages INT
DECLARE @dbname SYSNAME
DECLARE @dbsize DEC(15,0)
DECLARE @logsize DEC(15)
DECLARE @bytesperpage DEC(15,0)
DECLARE @pagesperMB DEC(15,0)
DECLARE @sql VARCHAR(1000)
IF (OBJECT_ID('#return_tbl') IS NOT NULL)
BEGIN
DROP TABLE #return_tbl
END
CREATE TABLE #return_tbl
( owner SYSNAME NOT NULL
, name SYSNAME NOT NULL
, id INT NOT NULL
, type VARCHAR(10) NOT NULL
, filegroup SYSNAME NOT NULL
, rows VARCHAR(100) NULL
, reserved DEC(15,0) NULL
, data DEC(15,0) NULL
, indexp DEC(15,0) NULL
, unused DEC(15,0) NULL
)
IF @table_type IS NULL
BEGIN
-- 기본 테이블 정보 --
INSERT INTO #return_tbl (owner, name, id, type, filegroup)
SELECT u.name "owner", o.name "name", o.id "id" ,'user' "type", f.groupname "filegroup"
FROM sysusers u INNER JOIN sysobjects o
ON u.uid = o.uid
INNER JOIN sysindexes i
ON o.id = i.id
INNER JOIN sysfilegroups f
ON i.groupid = f.groupid
WHERE xtype = 'u'
AND i.indid < 2
ORDER BY o.name
-- 테이블 spaceused 계산 시작
DECLARE cur_tbl CURSOR
FOR SELECT owner, name, id
FROM #return_tbl
OPEN cur_tbl
FETCH NEXT FROM cur_tbl
INTO @owner, @name, @oid
WHILE (@@fetch_status = 0)
BEGIN
/*******************************************************************/
-- #return_tbl.reserved
UPDATE #return_tbl
SET reserved = (SELECT sum(reserved)
FROM sysindexes
WHERE indid in (0,1,255)
AND id = @oid
)
WHERE CURRENT OF cur_tbl
-- #return_tbl.data
SELECT @pages = SUM(dpages)
FROM sysindexes
WHERE indid < 2
AND id = @oid
SELECT @pages = @pages + ISNULL(SUM(used), 0)
FROM sysindexes
WHERE indid = 255
AND id = @oid
UPDATE #return_tbl
SET data = @pages
WHERE CURRENT OF cur_tbl
-- #return_tbl.index
UPDATE #return_tbl
SET indexp = (SELECT SUM(used)
FROM sysindexes
WHERE indid IN (0,1,255)
AND id = @oid
) - data
WHERE CURRENT OF cur_tbl
-- #return_tbl.unused
UPDATE #return_tbl
SET unused = reserved - (SELECT SUM(used)
FROM sysindexes
WHERE indid IN (0,1,255)
AND id = @oid
)
WHERE CURRENT OF cur_tbl
-- #return_tbl.rows
UPDATE #return_tbl
SET rows = (SELECT rows
FROM sysindexes
WHERE indid < 2
AND id = @oid
)
WHERE CURRENT OF cur_tbl
/*******************************************************************/
FETCH NEXT FROM cur_tbl
INTO @owner, @name, @oid
END
CLOSE cur_tbl
DEALLOCATE cur_tbl
SELECT id "Id", owner "Owner", r.name "Name", filegroup "Filegroup", rows "Rows"
, CAST(reserved * d.low / (1000*1024) AS DEC(15,2)) "Reserved(MB)"
, CAST(data * d.low / (1000*1024) AS DEC(15,2)) "Data(MB)"
, CAST(indexp * d.low / (1000*1024) AS DEC(15,2)) "Index(MB)"
, CAST(unused * d.low / (1000*1024) AS DEC(15,2)) "Unused(MB)"
FROM #return_tbl r, master.dbo.spt_values d
WHERE d.number = 1
AND d.type = 'E'
ORDER BY filegroup, name
END
ELSE IF @table_type = 'system'
BEGIN
SELECT u.name "owner", o.name "name", 'system' "type"
FROM sysusers u INNER JOIN sysobjects o
ON u.uid = o.uid
WHERE xtype = 's'
ORDER BY o.name
END
ELSE IF @table_type = 'all'
BEGIN
SELECT u.name "owner", o.name "name",
CASE xtype
WHEN 'S' THEN 'system'
WHEN 'U' THEN 'user'
WHEN 'V' THEN 'view'
END "type"
FROM sysusers u INNER JOIN sysobjects o
ON u.uid = o.uid
WHERE xtype IN ('s', 'u', 'v')
ORDER BY xtype
END
ELSE IF @table_type = 'view'
BEGIN
SELECT u.name "owner", o.name "name", 'view' "type"
FROM sysusers u INNER JOIN sysobjects o
ON u.uid = o.uid
WHERE xtype = 'v'
ORDER BY o.name
END
ELSE
BEGIN
RAISERROR('''%s'' parameter is not valid...', 16, 1, @table_type)
RETURN(1)
END
DROP TABLE #return_tbl
SET NOCOUNT OFF
RETURN(0) --sp_table0
GO
EXEC sp_MS_marksystemobject 'sp_table0'
GO
GRANT EXECUTE ON sp_table0 TO PUBLIC
GO
/************************************************************************************************
-- sp_column0 'dbo.user_t'
-- 해당 테이블의 컬럼을 보여준다
-- 파라미터로는 테이블명을 입력해야 한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_column0') IS NOT NULL
DROP PROCEDURE sp_column0
GO
CREATE PROC sp_column0
@table_name VARCHAR(100) = null
AS
SET NOCOUNT ON
DECLARE @errormsg VARCHAR(100)
IF (@table_name IS NULL)
BEGIN
RAISERROR('''@table_name'' parameter is empty...', 16, 1, @table_name)
RETURN(1)
END
IF (CHARINDEX('.',@table_name) = 0)
BEGIN
IF NOT EXISTS (SELECT name
FROM sysobjects
WHERE name = @table_name
AND xtype IN ('s', 'u')
)
BEGIN
SET @errormsg = 'There isn''t ''%s'' table in ' + db_name()+ ' database...'
RAISERROR(@errormsg, 16, 1, @table_name)
RETURN(1)
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT name
FROM sysobjects
WHERE name = RIGHT(@table_name,LEN(@table_name)
-CHARINDEX('.',@table_name))
AND uid = USER_ID(LEFT(@table_name,LEN(@table_name)
- (LEN(@table_name)-CHARINDEX('.',@table_name)) - 1))
AND xtype IN ('s', 'u')
)
BEGIN
SET @errormsg = 'There isn''t ''%s'' table in ' + db_name()+ ' database...'
RAISERROR(@errormsg, 16, 1, @table_name)
RETURN(1)
END
END
SELECT c.name "col_name", t.name "type", c.length "length", c.isnullable "nullable"
FROM syscolumns c INNER JOIN systypes t
ON c.xusertype = t.xusertype
WHERE c.id = OBJECT_ID(@table_name)
RETURN(0) --sp_column0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_column0 TO PUBLIC
GO
/************************************************************************************************
-- sp_block0
-- 블러킹 대상과 블러킹된 대상리스트, BUFFER내의 데이터를 보여준다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_block0') IS NOT NULL
DROP PROCEDURE sp_block0
GO
CREATE PROC sp_block0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 처음 결과 시작
DECLARE @probclients TABLE(spid SMALLINT
, ecid SMALLINT
, blocked SMALLINT
, waittype BINARY(2)
, dbid SMALLINT
, ignore_app TINYINT
, PRIMARY KEY (blocked, spid, ecid)
)
INSERT @probclients
SELECT spid, ecid, blocked, waittype, dbid
, CASE WHEN CONVERT(VARCHAR(128),hostname) = 'PSSDIAG' THEN 1
ELSE 0
END
FROM sysprocesses
WHERE blocked!=0
OR waittype != 0x0000
INSERT @probclients
SELECT DISTINCT blocked, 0, 0, 0x0000, 0, 0
FROM @probclients
WHERE blocked NOT IN (SELECT spid
FROM @probclients
)
and blocked != 0
SELECT spid, status, blocked, open_tran, waitresource, waittype
, waittime, cmd, lastwaittype, cpu, physical_io
, memusage, last_batch=CONVERT(VARCHAR(26), last_batch,121)
, logIN_time=CONVERT(VARCHAR(26), logIN_time,121),net_address
, net_library, dbid, ecid, kpid, hostname, hostprocess
, loginame, program_name, nt_domain, nt_username, uid, sid
, sql_handle, stmt_start, stmt_end
FROM master..sysprocesses
WHERE blocked!=0
OR waittype != 0x0000
OR spid IN (SELECT blocked
FROM @probclients
WHERE blocked != 0
)
OR spid IN (SELECT spid
FROM @probclients
WHERE blocked != 0
)
-- 처음 결과 끝
-- 두번째 결과 시작
DECLARE @spid INT
DECLARE @msg VARCHAR(100)
CREATE TABLE #infoevent(id INT IDENTITY
, spid INT
, eventtype VARCHAR(255)
, parameters INT
, eventinfo VARCHAR(255)
)
DECLARE cur_infoevent CURSOR READ_ONLY
FOR SELECT spid
FROM @probclients
OPEN cur_infoevent
FETCH NEXT FROM cur_infoevent
INTO @spid
WHILE (@@fetch_status = 0)
BEGIN
SET @msg = 'dbcc inputbuffer (' + CONVERT(VARCHAR(20), @spid) + ')'
INSERT #infoevent (eventtype, parameters, eventinfo)
EXEC (@msg)
UPDATE #infoevent
SET spid = @spid
WHERE id = @@identity
FETCH NEXT FROM cur_infoevent
INTO @spid
END
CLOSE cur_infoevent
DEALLOCATE cur_infoevent
SELECT * from #infoevent
DROP TABLE #infoevent
-- 두번째 결과 끝
-- 세번째 결과 시작
SELECT CONVERT (SMALLINT, req_spid) As spid
, rsc_dbid As dbid
, db_name(rsc_dbid) As dBName
, rsc_objid As ObjId
, rsc_indid As IndId
, substring (v.name, 1, 4) As Type
, substring (rsc_text, 1, 16) as Resource
, substring (u.name, 1, 8) As Mode
, substring (x.name, 1, 5) As Status
INTO #BlockLock_t
FROM master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
WHERE master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and req_spid in (SELECT spid
FROM master..sysprocesses
WHERE blocked = 0 and spid IN (SELECT blocked
FROM master..sysprocesses
)
)
CREATE TABLE #lockreturn_t
( spid SMALLINT
, dbname SYSNAME
, objname SYSNAME
, indid SMALLINT
, type VARCHAR(5)
, resource VARCHAR(20)
, mode VARCHAR(10)
, status VARCHAR(5)
)
DECLARE @dbid INT
DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(300)
DECLARE cur_lockobject CURSOR
FOR
SELECT DISTINCT dbid, dbname
FROM #BlockLock_t
ORDER BY dbid
OPEN cur_lockobject
FETCH cur_lockobject INTO @dbid, @dbname
WHILE @@fetch_status <> -1
BEGIN
IF @@fetch_status <> -2
BEGIN
SET @sql = 'select #BlockLock_t.spid, #BlockLock_t.dbname, a.name, '
SET @sql = @sql + '#BlockLock_t.indid, #BlockLock_t.type, #BlockLock_t.resource, '
SET @sql = @sql + '#BlockLock_t.mode, #BlockLock_t.status '
SET @sql = @sql + 'FROM #BlockLock_t, '
SET @sql = @sql + @dbname+'..sysobjects a WHERE dbid = '
SET @sql = @sql + CAST(@dbid AS VARCHAR(10))
SET @sql = @sql + ' AND #BlockLock_t.ObjID = a.id ORDER BY spid'
INSERT INTO #lockreturn_t EXEC(@sql)
END
FETCH cur_lockobject INTO @dbid, @dbname
END
SELECT *
FROM #lockreturn_t
ORDER BY spid, dbname, objname
CLOSE cur_lockobject
DEALLOCATE cur_lockobject
DROP TABLE #BlockLock_t
DROP TABLE #lockreturn_t
-- 세번째 결과 끝
SET NOCOUNT OFF
RETURN(0) --sp_block0
GO
GRANT EXECUTE ON sp_block0 TO PUBLIC
GO
/************************************************************************************************
-- sp_server0
-- 서버의 기본 정보를 보여준다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_server0') IS NOT NULL
DROP PROCEDURE sp_server0
GO
CREATE PROC sp_server0
AS
SET NOCOUNT ON
SELECT SERVERPROPERTY('ServerName') AS ServerName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('InstanceName') AS InstanceName
, SERVERPROPERTY('Edition') AS Edition
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') AS ProductLevel
EXEC master..xp_msver
RETURN(0) --sp_server0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_server0 TO PUBLIC
GO
/************************************************************************************************
-- sp_dboption0
-- 데이터베이스 설정 옵션을 보여준다.
-- 파라미넡가 null인 경우 전체 DB의 옵션을 보여준다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_dboption0') IS NOT NULL
DROP PROCEDURE sp_dboption0
GO
CREATE PROC sp_dboption0
@dbname SYSNAME = null
AS
SET NOCOUNT ON
DECLARE @errmsg VARCHAR(100)
DECLARE @sql VARCHAR(1000)
DECLARE @db VARCHAR(100)
CREATE TABLE #return
( dbname SYSNAME NULL
, options varchar(100) NULL
)
IF (@dbname IS NOT NULL)
BEGIN
IF NOT EXISTS (SELECT name
FROM master..sysdatabases
WHERE name = @dbname
)
BEGIN
SET @errmsg = 'There is no ''%s'' Database in this SQL Server...'
RAISERROR(@errmsg, 16,1, @dbname)
RETURN(1)
END
ELSE
BEGIN
SET @sql = 'sp_dboption ''' + @dbname + ''''
EXEC (@sql)
END
END
ELSE
BEGIN -- @dbname is null
DECLARE cur_dboption CURSOR READ_ONLY
FOR SELECT name
FROM master..sysdatabases
OPEN cur_dboption
FETCH NEXT FROM cur_dboption
INTO @db
WHILE (@@fetch_status = 0)
BEGIN
SET @sql = 'sp_dboption ''' + @db + ''''
INSERT #return (options)
EXEC (@sql)
UPDATE #return
SET dbname = @db
WHERE dbname IS NULL
FETCH NEXT FROM cur_dboption
INTO @db
END
CLOSE cur_dboption
DEALLOCATE cur_dboption
SELECT *
FROM #return
DROP TABLE #return
END
RETURN(0) --sp_dboption0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_dboption0 TO PUBLIC
GO
/************************************************************************************************
-- sp_filegroup0
-- 해당 DB의 파일&파일 그룹 정보를 보여준다.
-- 파라미터가 'all'일시 전체 DB의 파일&파일 그룹 정보를 보여준다.
************************************************************************************************/
USE master
GO
IF OBJECT_ID('sp_filegroup0') IS NOT NULL
DROP PROCEDURE sp_filegroup0
GO
CREATE PROC sp_filegroup0
@param VARCHAR(5) = NULL
AS
SET NOCOUNT ON
IF (@param IS NOT NULL AND @param != 'all')
BEGIN
RAISERROR('''@param'' parameter have to be NULL or ''all''...', 16, 1)
RETURN(1)
END
DECLARE @db_name SYSNAME
DECLARE @cdbname SYSNAME
DECLARE @sql VARCHAR(1000)
DECLARE @dblist TABLE
( dbname SYSNAME
)
CREATE TABLE #result_tbl
( dbname SYSNAME
, groupname VARCHAR(250)
, filename VARCHAR(250)
, filepath VARCHAR(250)
, filesize DEC(15,2)
)
IF (@param = 'all')
INSERT INTO @dblist
SELECT name
FROM master..sysdatabases
WHERE name not in ('master','model','msdb','tempdb','pubs','northwind')
ELSE
BEGIN
SET @db_name = db_name()
IF NOT EXISTS (SELECT name
FROM master..sysdatabases
WHERE name = @db_name)
BEGIN
RAISERROR('There is no ''%s'' database in this SQL Server...', 16,1,@db_name)
RETURN(1)
END
ELSE
INSERT INTO @dblist
VALUES (@db_name)
END
DECLARE cur_dblist CURSOR FOR
SELECT dbname
FROM @dblist
OPEN cur_dblist
FETCH NEXT FROM cur_dblist
INTO @cdbname
WHILE @@FETCH_status = 0
BEGIN
SET @sql = ''
SET @sql = @sql + 'SELECT ''' + @cdbname
SET @sql = @sql + ''' AS dbname, isnull(b.groupname, '''
SET @sql = @sql + 'Transaction Log' + ''') AS groupname, rtrim(a.name) AS filename,'
SET @sql = @sql + ' rtrim(a.filename) AS filepath, '
SET @sql = @sql + 'CAST(CAST(a.size AS DEC(15,2))*8/1000. AS DEC(15,2)) AS filesize '
SET @sql = @sql + 'FROM ' + @cdbname + '.dbo.sysfiles a LEFT OUTER JOIN '
SET @sql = @sql + @cdbname + '.dbo.sysfilegroups b '
SET @sql = @sql + 'on a.groupid = b.groupid '
SET @sql = @sql + 'ORDER BY a.fileid '
INSERT INTO #result_tbl exec(@sql)
FETCH NEXT FROM cur_dblist
INTO @cdbname
END
CLOSE cur_dblist
DEALLOCATE cur_dblist
SELECT dBName, groupname "FileGroup", FileName, FileSize, FilePath
FROM #result_tbl
DROP TABLE #result_tbl
SET NOCOUNT OFF
RETURN(0) --sp_filegroup0
GO
GRANT EXECUTE ON sp_filegroup0 TO PUBLIC
GO
/************************************************************************************************
-- sp_lock0
-- 락 정보를 보여준다.
-- 첫번째 파라미터는 server process id to check for locks
-- 두번째 파라미터는 other process id to check for locks
************************************************************************************************/
USE master
GO
IF OBJECT_ID('sp_lock0') IS NOT NULL
DROP PROCEDURE sp_lock0
GO
CREATE PROC sp_lock0
@spid1 INT = NULL,
@spid2 INT = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @objid INT,
@indid INT,
@dbid INT,
@string VARCHAR(255)
CREATE TABLE #locktable
( spid SMALLINT
, loginname VARCHAR(20)
, hostname VARCHAR(30)
, dbid INT
, dbname SYSNAME
, ObjOwner VARCHAR(128)
, objId INT
, ObjName VARCHAR(128)
, IndId INT
, IndName VARCHAR(128)
, Type VARCHAR(4)
, Resource VARCHAR(16)
, Mode VARCHAR(8)
, Status VARCHAR(5)
)
IF @spid1 IS NOT NULL
BEGIN
INSERT #locktable( spid, loginname, hostname, dbid, dbname, ObjOwner, objId, ObjName
, IndId, IndName, Type, Resource, Mode, Status
)
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 20),'')
, COALESCE(SUBSTRING (s.hostname, 1, 30),'')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 20)
, ''
, l.rsc_objid
, ''
, l.rsc_indid
, ''
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
WHERE l.rsc_type = v.number
AND v.type = 'LR'
AND l.req_status = x.number
AND x.type = 'LS'
AND l.req_mode + 1 = u.number
AND u.type = 'L'
AND req_spid in (@spid1, @spid2)
AND req_spid = s.spid
END
ELSE
BEGIN
INSERT #locktable( spid, loginname, hostname, dbid, dbname, ObjOwner, objId, ObjName
, IndId, IndName, Type, Resource, Mode, Status
)
SELECT CONVERT (SMALLINT, l.req_spid)
, COALESCE(SUBSTRING (s.loginame, 1, 20),'')
, COALESCE(SUBSTRING (s.hostname, 1, 30),'')
, l.rsc_dbid
, SUBSTRING (DB_NAME(l.rsc_dbid), 1, 20)
, ''
, l.rsc_objid
, ''
, l.rsc_indid
, ''
, SUBSTRING (v.name, 1, 4)
, SUBSTRING (l.rsc_text, 1, 16)
, SUBSTRING (u.name, 1, 8)
, SUBSTRING (x.name, 1, 5)
FROM master.dbo.syslockinfo l,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u,
master.dbo.sysprocesses s
WHERE l.rsc_type = v.number
AND v.type = 'LR'
AND l.req_status = x.number
AND x.type = 'LS'
AND l.req_mode + 1 = u.number
AND u.type = 'L'
AND req_spid = s.spid
ORDER BY spID
END
DECLARE cur_lock2 CURSOR
FOR SELECT dbid, ObjId, IndId
FROM #locktable
WHERE Type <>'DB' AND Type <> 'FIL'
OPEN cur_lock2
FETCH NEXT FROM cur_lock2 INTO @dbid, @ObjId, @IndId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @string =
'USE ' + DB_NAME(@dbid) + CHAR(13)
+ 'update #locktable set ObjName = name, ObjOwner = USER_NAME(uid)'
+ ' FROM sysobjects WHERE id = ' + CONVERT(VARCHAR(32),@objid)
+ ' AND ObjId = ' + CONVERT(VARCHAR(32),@objid)
+ ' AND dbid = ' + CONVERT(VARCHAR(32),@dbId)
EXEC (@string)
SELECT @string =
'USE ' + DB_NAME(@dbid) + CHAR(13)
+ 'update #locktable set IndName = i.name FROM sysindexes i '
+ ' WHERE i.id = ' + CONVERT(VARCHAR(32),@objid)
+ ' AND i.indid = ' + CONVERT(VARCHAR(32),@indid)
+ ' AND ObjId = ' + CONVERT(VARCHAR(32),@objid)
+ ' AND dbid = ' + CONVERT(VARCHAR(32),@dbId)
+ ' AND #locktable.indid = ' + CONVERT(VARCHAR(32),@indid)
EXEC (@string)
FETCH NEXT FROM cur_lock2 INTO @dbid, @ObjId, @IndId
END
CLOSE cur_lock2
DEALLOCATE cur_lock2
SELECT * FROM #locktable
DROP TABLE #locktable
RETURN(0) --sp_lock0
GO
GRANT EXECUTE ON sp_lock0 TO PUBLIC
GO
/************************************************************************************************
-- sp_index0
-- 해당 DB의 인덱스를 모두 보여준다.
************************************************************************************************/
USE master
GO
IF OBJECT_ID('sp_index0') IS NOT NULL
DROP PROCEDURE sp_index0
GO
CREATE PROC sp_index0
@objname VARCHAR(100) = NULL
AS
SET NOCOUNT ON
IF (@objname IS NULL)
BEGIN
SET @objname = 'all_indexes_output'
END
ELSE
BEGIN
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
END
CREATE TABLE #indexes_tbl
( tblname VARCHAR(255)
, idxname VARCHAR(255)
, idxdesc VARCHAR(1000)
, idxcol VARCHAR(1000)
)
DECLARE @ignore_duplicate_keys VARCHAR(35) -- spt_values' name
, @unique VARCHAR(35)
, @ignore_duplicate_rows VARCHAR(35)
, @hypothetical VARCHAR(35)
, @statistics VARCHAR(35)
, @primary_key VARCHAR(35)
, @unique_key VARCHAR(35)
, @auto_create VARCHAR(35)
, @stats_no_recompute VARCHAR(35)
SELECT @ignore_duplicate_keys = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 1
SELECT @unique = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 2
SELECT @ignore_duplicate_rows = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 4
SELECT @hypothetical = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 32
SELECT @statistics = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 64
SELECT @primary_key = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 2048
SELECT @unique_key = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 4096
SELECT @auto_create = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 8388608
SELECT @stats_no_recompute = name FROM master.dbo.spt_values
WHERE type = 'I' AND number = 16777216
INSERT INTO #indexes_tbl
SELECT o.name "Table Name"
, i.name "Index Name"
--bits 16 off, 1, 2, 16777216 on, located on group
, CONVERT(VARCHAR(210)
, CASE WHEN (i.status & 16)<>0 THEN 'clustered'
ELSE 'nonclustered'
END
+ CASE WHEN (i.status & 1)<>0 THEN ', '+ @ignore_duplicate_keys
ELSE ''
END
+ CASE WHEN (i.status & 2)<>0 THEN ', '+ @unique
ELSE ''
END
+ CASE WHEN (i.status & 4)<>0 THEN ', '+ @ignore_duplicate_rows
ELSE ''
END
+ CASE WHEN (i.status & 64)<>0 THEN ', '+ @statistics
ELSE CASE WHEN (i.status & 32)<>0 THEN ', '+ @hypothetical
ELSE ''
END
END
+ CASE WHEN (i.status & 2048)<>0 THEN ', '+ @primary_key
ELSE ''
END
+ CASE WHEN (i.status & 4096)<>0 THEN ', '+ @unique_key
ELSE ''
END
+ CASE WHEN (i.status & 8388608)<>0 THEN ', '+ @auto_create
ELSE ''
END
+ CASE WHEN (i.status & 16777216)<>0 THEN ', '+ @stats_no_recompute
ELSE ''
END
) "Index Description"
, LEFT(ISNULL(INDEX_COL(o.name,indid, 1)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 2)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 3)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 4)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 5)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 6)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 7)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 8)+ ', ','')
,len(ISNULL(INDEX_COL(o.name,indid, 1)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 2)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 3)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 4)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 5)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 6)+ ', ','')
+ ISNULL(INDEX_COL(o.name,indid, 7)+', ','')
+ ISNULL(INDEX_COL(o.name,indid, 8)+ ', ','')
) - 1
) "Indexed Column Name"
FROM sysindexes i INNER JOIN sysobjects o
ON i.id = o.id
WHERE i.indid > 0
AND i.indid < 255
AND o.type = 'U'
--exclude autostatistic index
AND (i.status & 64) = 0
AND (i.status & 8388608) = 0
AND (i.status & 16777216)= 0
ORDER BY o.name
IF (@objname = 'all_indexes_output')
SELECT *
FROM #indexes_tbl
ELSE
SELECT *
FROM #indexes_tbl
WHERE tblname = @objname
DROP TABLE #indexes_tbl
RETURN(0) --sp_index0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_index0 TO PUBLIC
GO
/************************************************************************************************
-- sp_filepageno0
-- BINARY의 first값에서 파일번호와 페이지번호를 DECIMAL로 출력한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_filepageno0') IS NOT NULL
DROP PROCEDURE sp_filepageno0
GO
CREATE PROC sp_filepageno0
@first BINARY(6) = NULL
AS
SET NOCOUNT ON
DECLARE @msg VARCHAR(100)
DECLARE @file_id INT
DECLARE @page_id INT
IF (@first IS NULL)
BEGIN
SET @msg = '''@first'' parameter is null. You can get @first from Sysindexes Table...'
RAISERROR(@msg, 16, 1, @first)
RETURN(1)
END
SET @file_id = convert(varchar(5), (convert(int, substring(@first, 6, 1)) * power(2, 8))
+ (convert(int, substring(@first, 5, 1)))
)
SET @page_id = convert(varchar(11), (convert(int, substring(@first, 4, 1)) * power(2, 24))
+ (convert(int, substring(@first, 3, 1)) * power(2, 16))
+ (convert(int, substring(@first, 2, 1)) * power(2, 8))
+ (convert(int, substring(@first, 1, 1)))
)
SELECT @file_id "FILE_ID", @page_id "PAGE_ID"
RETURN(0) --sp_filepageno0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_filepageno0 TO PUBLIC
GO
/************************************************************************************************
-- sp_constraint0
-- 해당 DB 전체의 제약사항이나 특정 오브젝트의 제약사항을 출력한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_constraint0') IS NOT NULL
DROP PROCEDURE sp_constraint0
GO
CREATE PROC sp_constraint0
@objname VARCHAR(100) = NULL
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(1000)
SET @dbname = db_name()
SET @sql = ''
IF (@objname IS NULL)
BEGIN
SET @sql = 'SELECT object_name(id) "OBEJCT_NAME", object_name(constid) "CONST_NAME" '
SET @sql = @sql + 'FROM ' + @dbname + '..sysconstraints '
SET @sql = @sql + 'ORDER BY object_name(id)'
EXEC (@sql)
END
ELSE
BEGIN
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
SET @sql = 'SELECT object_name(id) "OBEJCT_NAME", object_name(constid) "CONST_NAME" '
SET @sql = @sql + 'FROM ' + @dbname + '..sysconstraints WHERE id = '
SET @sql = @sql + CAST(object_id(@objname) as VARCHAR(10))
SET @sql = @sql + 'ORDER BY object_name(id)'
EXEC (@sql)
END
RETURN(0) --sp_constraint0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_constraint0 TO PUBLIC
GO
/************************************************************************************************
-- sp_trigger0
-- 해당 DB의 모든 트리거나 특정 오브젝트의 트리거를 출력한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_trigger0') IS NOT NULL
DROP PROCEDURE sp_trigger0
GO
CREATE PROC sp_trigger0
@objname VARCHAR(100) = NULL
AS
SET NOCOUNT ON
IF (@objname IS NULL)
SELECT name "TRG_NAME"
, user_name(uid) "TRG_OWNER"
, ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"
, ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"
, ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"
, ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"
, ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSEAD_OF_TRG"
FROM sysobjects
WHERE type = 'TR'
ELSE
BEGIN
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
SELECT name "TRG_NAME"
, user_name(uid) "TRG_OWNER"
, ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"
, ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"
, ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"
, ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"
, ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSTEADOF_TRG"
FROM sysobjects
WHERE parent_obj = object_id(@objname)
AND type = 'TR'
END
RETURN(0) --sp_trigger0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_trigger0 TO PUBLIC
GO
/************************************************************************************************
-- sp_converthex0
-- 해당 오브젝트의 인덱스 관련 파일과 페이지번호를 전체 출력한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_converthex0') IS NOT NULL
DROP PROCEDURE sp_converthex0
GO
CREATE PROC sp_converthex0
@objname VARCHAR(100) = NULL
AS
SET NOCOUNT ON
IF (@objname IS NULL)
BEGIN
RAISERROR('''@objname'' parameter is empty... ', 16, 1)
RETURN(1)
END
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
SELECT convert(char(30), name) AS 'object_name',
id,
indid,
convert(varchar(2), (convert(int, substring(first, 6, 1)) * power(2, 8))
+ (convert(int, substring(first, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(first, 4, 1)) * power(2, 24)) +
(convert(int, substring(first, 3, 1)) * power(2, 16)) +
(convert(int, substring(first, 2, 1)) * power(2, 8)) +
(convert(int, substring(first, 1, 1)))) AS "firstDec",
first,
convert(varchar(2), (convert(int, substring(root, 6, 1)) * power(2, 8))
+ (convert(int, substring(root, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(root, 4, 1)) * power(2, 24)) +
(convert(int, substring(root, 3, 1)) * power(2, 16)) +
(convert(int, substring(root, 2, 1)) * power(2, 8)) +
(convert(int, substring(root, 1, 1)))) AS "rootDec",
root,
convert(varchar(2), (convert(int, substring(firstIAM, 6, 1)) * power(2, 8))
+ (convert(int, substring(firstIAM, 5, 1)))) + ':' +
convert(varchar(11),
(convert(int, substring(firstIAM, 4, 1)) * power(2, 24)) +
(convert(int, substring(firstIAM, 3, 1)) * power(2, 16)) +
(convert(int, substring(firstIAM, 2, 1)) * power(2, 8)) +
(convert(int, substring(firstIAM, 1, 1)))) AS "firstIAMDec",
firstIAM
FROM sysindexes
WHERE id = object_id(@objname)
RETURN(0) --sp_converthex0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_converthex0 TO PUBLIC
GO
/************************************************************************************************
-- sp_spaceused0
-- 서버의 전체 DB에 대한 사이즈를 출력한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_spaceused0') IS NOT NULL
DROP PROCEDURE sp_spaceused0
GO
CREATE PROC sp_spaceused0
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(1000)
DECLARE @pages INT
DECLARE @dbsize DEC(15,0)
DECLARE @logsize DEC(15)
DECLARE @str VARCHAR(2000)
CREATE TABLE #spt_space
( dbname SYSNAME NOT NULL
, pages INT NULL
, dbsize DEC(15,0) NULL
, logsize DEC(15,0) NULL
, bytesperpage DEC(15,0) NULL
, pagesperMB DEC(15,0) NULL
, reserved DEC(15) NULL
, data DEC(15) NULL
, indexp DEC(15) NULL
, unused DEC(15) NULL
)
INSERT INTO #spt_space(dbname)
SELECT name
FROM master.dbo.sysdatabases
WHERE name not in ('master','msdb','model','tempdb','pubs','northwind')
DECLARE cur_db_size CURSOR READ_ONLY
FOR
SELECT dbname
FROM #spt_space
OPEN cur_db_size
FETCH NEXT FROM cur_db_size
INTO @dbname
WHILE @@FETCH_status = 0
BEGIN
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET dbsize = (SELECT SUM(CONVERT(DEC(15),size)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysfiles '
SET @str = @str + ' WHERE (status & 64 = 0) '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET logsize = (SELECT SUM(CONVERT(DEC(15),size)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysfiles '
SET @str = @str + ' WHERE (status & 64 <> 0) '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET bytesperpage = (SELECT low '
SET @str = @str + ' FROM master.dbo.spt_values '
SET @str = @str + ' WHERE number = 1 AND type = ''E'' '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET pagesperMB = 1048576 / (SELECT bytesperpage '
SET @str = @str + ' FROM #spt_space '
SET @str = @str + ' WHERE dbname = ''' + @dbname + ''''
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET reserved = (SELECT SUM(CONVERT(DEC(15),reserved)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysindexes '
SET @str = @str + ' WHERE indid in (0, 1, 255) '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
-- data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET pages = (SELECT SUM(CONVERT(DEC(15),dpages)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysindexes '
SET @str = @str + ' WHERE indid < 2 '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET pages = pages + (SELECT + ISNULL(SUM(CONVERT(DEC(15),used)), 0) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysindexes '
SET @str = @str + ' WHERE indid = 255 '
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET data = (SELECT pages '
SET @str = @str + ' FROM #spt_space '
SET @str = @str + ' WHERE dbname = ''' + @dbname + ''''
SET @str = @str + ' ) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
-- index: sum(used) where indid in (0, 1, 255) - @data
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET indexp = (SELECT SUM(CONVERT(DEC(15),used)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysindexes '
SET @str = @str + ' WHERE indid IN (0, 1, 255) '
SET @str = @str + ' ) - data '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
-- unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
SET @str = ''
SET @str = @str + 'UPDATE #spt_space '
SET @str = @str + 'SET unused = reserved - (SELECT SUM(CONVERT(DEC(15),used)) '
SET @str = @str + ' FROM ' + @dbname + '.dbo.sysindexes '
SET @str = @str + ' WHERE indid IN (0, 1, 255)) '
SET @str = @str + 'WHERE dbname = ''' + @dbname + ''''
EXEC(@str)
FETCH NEXT FROM cur_db_size
INTO @dbname
END
CLOSE cur_db_size
DEALLOCATE cur_db_size
SELECT
dbname AS "dBName"
, CONVERT(DEC(10,2),(dbsize + logsize) / pagesperMB) AS "DiskSize(MB)"
, CONVERT(DEC(10,2),(reserved * bytesperpage / 1024) / 1000) AS "ReservedSize(MB)"
, CONVERT(DEC(10,2),(data * bytesperpage / 1024) / 1000) AS "DataSize(MB)"
, CONVERT(DEC(10,2),(indexp * bytesperpage / 1024) / 1000) AS "IndexSize(MB)"
, CONVERT(DEC(10,2),(unused * bytesperpage / 1024) / 1000) AS "UnusedSize(MB)"
FROM #spt_space
DROP TABLE #spt_space;
RETURN(0) --sp_spaceused0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_spaceused0 TO PUBLIC
GO
/************************************************************************************************
-- sp_rowlength0
-- 해당 DB의 Table, View의 Row길이를 출력한다.
************************************************************************************************/
USE master
GO
IF OBJECT_ID('sp_rowlength0') IS NOT NULL
DROP PROCEDURE sp_rowlength0
GO
CREATE PROC sp_rowlength0
AS
SET NOCOUNT ON
SELECT object_name(b.id) "Obj_Name", a.xtype, SUM(b.length) "Length"
FROM sysobjects a INNER JOIN syscolumns b
ON a.id = b.id
WHERE a.xtype IN ('u','v','S')
AND object_name(b.id) NOT LIKE 'dt_%'
GROUP BY object_name(b.id), a.xtype
ORDER BY CASE A.XTYPE WHEN 'U' THEN 1
WHEN 'V' THEN 2
WHEN 'S' THEN 3
END
SET NOCOUNT OFF
RETURN(0) --sp_rowlength0
GO
GRANT EXECUTE ON sp_rowlength0 TO PUBLIC
GO
/************************************************************************************************
-- sp_freedisksize0
-- 물리적 드라이브 가용량을 표시한다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_freedisksize0') IS NOT NULL
DROP PROCEDURE sp_freedisksize0
GO
CREATE PROC sp_freedisksize0
AS
SET NOCOUNT ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @free INT
DECLARE @query VARCHAR(1300)
DECLARE @MB BIGINT
SET @MB = 1048576
SET @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives
( drive CHAR(1) PRIMARY KEY
, FreeSize INT NULL
, TotalSize INT NULL
)
INSERT #drives(drive,FreeSize) EXEC @query
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE cur_freespace CURSOR FAST_FORWARD
FOR SELECT drive
FROM #drives
ORDER by drive
OPEN cur_freespace
FETCH NEXT FROM cur_freespace INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM cur_freespace INTO @drive
END
CLOSE cur_freespace
DEALLOCATE cur_freespace
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT drive "Drive"
, totalsize "TotalSize(MB)"
, freesize "FreeSize(MB)"
, CAST(CAST(freesize as DEC(10,2)) / CAST(totalsize AS DEC(10,2))
* 100 AS DEC(10,2)) "FreeSize(%)"
FROM #drives
DROP TABLE #drives
RETURN(0) --sp_freedisksize0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_freedisksize0 TO PUBLIC
GO
/************************************************************************************************
-- sp_object0
-- 파라미터 없이 모든 오브젝트를 표시하고 특정 파라미터 기입시 상세 설명이 출력된다.
-- 'help' 파라미터시 파라미터 help manual이 출력된다.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_object0') IS NOT NULL
DROP PROCEDURE sp_object0
GO
CREATE PROC sp_object0
@param VARCHAR(100) = NULL
AS
SET NOCOUNT ON
DECLARE @str VARCHAR(2000)
, @fk_name SYSNAME
, @fk_table SYSNAME
, @rk_table SYSNAME
, @fkey VARCHAR(100)
, @rkey VARCHAR(100)
, @fkeys VARCHAR(100)
, @rkeys VARCHAR(100)
SET @fkey = ''
SET @rkey = ''
SET @fkeys = ''
SET @rkeys = ''
CREATE TABLE #temp_tbl
( tblname VARCHAR(255)
, idxname VARCHAR(255)
, idxdesc VARCHAR(1000)
, idxcol VARCHAR(1000)
)
CREATE TABLE #fk_tbl
( fk_name SYSNAME
, foreign_table VARCHAR(100)
, foreign_column VARCHAR(100)
, delete_action VARCHAR(10)
, update_action VARCHAR(10)
, reference_table VARCHAR(100)
, reference_column VARCHAR(100)
)
IF (@param IS NOT NULL) AND
(@param NOT IN ('S','U','V','PK','UQ','FK','SP','SF','IF'
,'TF','TR','XP','L', 'help','CK','DF'))
BEGIN
RAISERROR('''%s'' is not a designated parameter.
Please use ''help'' parameter.', 16, 1,@param)
RETURN(1)
END
IF (@param IS NULL)
BEGIN
SELECT "ObjectType" =
CASE xtype WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'L' THEN 'LOG'
WHEN 'FN' THEN 'SCHOLAR FUNCTION'
WHEN 'IF' THEN 'INLINE TABLE FUNCTION'
WHEN 'P' THEN 'USER PROCEDURE'
WHEN 'PK' THEN 'PRIMARY KEY'
WHEN 'S' THEN 'SYSTEM TABLE'
WHEN 'TF' THEN 'TABLE FUNTION'
WHEN 'TR' THEN 'TRIGGER'
WHEN 'U' THEN 'USER TABLE'
WHEN 'UQ' THEN 'UNIQUE'
WHEN 'D' THEN 'Default'
WHEN 'C' THEN 'Cehck'
WHEN 'V' THEN 'VIEW'
WHEN 'X' THEN 'EXTENDED PROCEDURE'
END
, OBJECT_NAME(parent_obj) "ParentObject"
, name "ObjectName"
FROM sysobjects
WHERE xtype != 'S'
ORDER BY CASE xtype WHEN 'S' THEN 1
WHEN 'U' THEN 2
WHEN 'V' THEN 3
WHEN 'PK' THEN 4
WHEN 'UQ' THEN 5
WHEN 'D' THEN 6
WHEN 'C' THEN 7
WHEN 'F' THEN 8
WHEN 'P' THEN 9
WHEN 'FN' THEN 10
WHEN 'IF' THEN 11
WHEN 'TF' THEN 12
WHEN 'TR' THEN 13
WHEN 'X' THEN 14
WHEN 'L' THEN 15
ELSE 16
END
RETURN(0)
END
IF (@param = 'help')
BEGIN
PRINT ' SUBJECT'
PRINT ' This procedure is for viewing all objects in selected database. '
PRINT ' '
PRINT ' DESCRIPTION'
PRINT ' EXEC sp_object0'
PRINT ' View all objects in selected database.'
PRINT ' '
PRINT ' EXEC sp_object0 ''help'''
PRINT ' View help manual with usage, parameter.'
PRINT ' '
PRINT ' EXEC sp_object0 ''<parameter>'''
PRINT ' View specified object by a parameter.'
PRINT ' '
PRINT ' PARAMETER'
PRINT ' S : System Table'
PRINT ' U : User Table'
PRINT ' V : View'
PRINT ' PK : Primary Key'
PRINT ' UQ : Unique'
PRINT ' FK : Foreign Key'
PRINT ' SP : Stored Procedure'
PRINT ' XP : Extened Procedure'
PRINT ' SF : Scholar Function'
PRINT ' IF : Inline Table Function'
PRINT ' TF : Table Function'
PRINT ' TR : Trigger'
PRINT ' CK : Check'
PRINT ' DF : Default'
PRINT ' L : Log'
PRINT ' '
PRINT ' END'
RETURN(0)
END
IF (@param in ('S','U','V'))
BEGIN
SELECT name "ObjectName"
FROM sysobjects
WHERE xtype = @param
AND status >= CASE WHEN @param ='S' THEN -2147483648
WHEN @param IN ('U','V') THEN 0
END
RETURN(0)
END
IF (@param in ('PK', 'UQ'))
BEGIN
INSERT INTO #temp_tbl EXEC('sp_index0')
SELECT OBJECT_NAME(s.parent_obj) "TableName", s.name "Name", t.idxcol "ColumnName"
FROM sysobjects s INNER JOIN #temp_tbl t
ON OBJECT_NAME(s.parent_obj) = t.tblname
WHERE s.xtype = @param
AND t.idxdesc LIKE CASE @param WHEN 'PK' THEN '%primary key%'
WHEN 'UQ' THEN '%unique key%'
END
RETURN(0)
END
IF (@param in ('FK'))
BEGIN
INSERT INTO #fk_tbl(fk_name, foreign_table, reference_table, delete_action, update_action)
SELECT o.name, object_name(o.parent_obj), object_name(r.rkeyid)
, CASE ObjectProperty(r.constid, 'CnstIsDeleteCascade') WHEN 1 THEN 'Cascade'
WHEN 0 THEN 'No Action'
END
, CASE ObjectProperty(r.constid, 'CnstIsUpdateCascade') WHEN 1 THEN 'Cascase'
WHEN 0 THEN 'No Action'
END
FROM sysobjects o INNER JOIN sysreferences r
ON o.id = r.constid
WHERE xtype = 'F'
DECLARE cur_fk_col CURSOR
FOR SELECT fk_name, foreign_table, reference_table
FROM #fk_tbl
OPEN cur_fk_col
FETCH NEXT FROM cur_fk_col
INTO @fk_name, @fk_table, @rk_table
WHILE (@@fetch_status = 0)
BEGIN
SET @rkeys = ''
SET @fkeys = ''
DECLARE cur_fk_col2 CURSOR
FOR SELECT rkey, fkey
FROM sysforeignkeys
WHERE constid = object_id(@fk_name)
OPEN cur_fk_col2
FETCH NEXT FROM cur_fk_col2
INTO @rkey, @fkey
WHILE (@@fetch_status = 0)
BEGIN
SET @rkeys = @rkeys + col_name(object_id(@rk_table),@rkey) + ', '
SET @fkeys = @fkeys + col_name(object_id(@fk_table),@fkey) + ', '
FETCH NEXT FROM cur_fk_col2
INTO @rkey, @fkey
END
CLOSE cur_fk_col2
DEALLOCATE cur_fk_col2
UPDATE #fk_tbl
SET foreign_column = '(' + SUBSTRING(@fkeys,1, LEN(@fkeys)-1) + ')'
, reference_column = '(' + SUBSTRING(@rkeys,1, LEN(@rkeys)-1) + ')'
WHERE CURRENT OF cur_fk_col
FETCH NEXT FROM cur_fk_col
INTO @fk_name, @fk_table, @rk_table
END
CLOSE cur_fk_col
DEALLOCATE cur_fk_col
SELECT * FROM #fk_tbl
RETURN(0)
END
IF (@param in ('SP','SF','IF','TF','XP'))
BEGIN
SELECT name "ObjectName"
FROM sysobjects
WHERE xtype = CASE @param WHEN 'SP' THEN 'P'
WHEN 'SF' THEN 'FN'
WHEN 'XP' THEN 'X'
ELSE @param
END
AND status >= CASE WHEN @param ='XP' THEN -2147483648
WHEN @param IN ('SP','SF','IF','TF') THEN 0
END
RETURN(0)
END
IF (@param in ('L'))
BEGIN
PRINT 'There is no finish scripting with Log...'
RETURN(0)
END
IF (@param = 'TR')
BEGIN
SELECT name "TRG_NAME"
, user_name(uid) "TRG_OWNER"
, ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"
, ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"
, ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"
, ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"
, ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSEAD_OF_TRG"
FROM sysobjects
WHERE type = @param
RETURN(0)
END
IF (@param in ('DF','CK'))
BEGIN
SELECT so.name "Name"
, OBJECT_NAME(so.parent_obj) "TableName"
, cl.name "ColumnName"
, cm.text "Contents"
FROM sysobjects so INNER JOIN sysconstraints cs
ON so.id = cs.constid
INNER JOIN syscomments cm
ON so.id = cm.id
INNER JOIN syscolumns cl
ON so.parent_obj = cl.id
AND cs.colid = cl.colid
WHERE so.xtype = CASE @param WHEN 'DF' THEN 'D'
WHEN 'CK' THEN 'C'
END
AND cm.colid = 1
RETURN(0)
END
DROP TABLE #temp_tbl
DROP TABLE #fk_tbl
RETURN(0) -- sp_object0
SET NOCOUNT OFF
GO
EXEC sp_MS_marksystemobject 'sp_object0'
GO
GRANT EXECUTE ON sp_object0 TO PUBLIC
GO
/************************************************************************************************
-- sp_showcontig0
-- 인덱스 조각화 정보를 전체와 기준값을 기준으로 보여준다.
-- 처음 파라미터는 조각화 수치 기준으로 필터링.
-- 두번째 파라미터는 해당 테이블만 적용.
************************************************************************************************/
USE MASTER
GO
IF OBJECT_ID('sp_showcontig0') IS NOT NULL
DROP PROCEDURE sp_showcontig0
GO
CREATE PROC sp_showcontig0
@value decimal(3) = 0
, @objname varchar(4) = NULL
AS
SET NOCOUNT ON
IF (@objname IS NOT NULL)
BEGIN
IF NOT EXISTS(SELECT ID
FROM sysobjects
WHERE id = object_id(@objname)
AND xtype IN ('S','U', 'V')
)
BEGIN
RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)
RETURN(1)
END
END
DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(300)
DECLARE @tblname SYSNAME
DECLARE @idxname SYSNAME
CREATE TABLE #tableindex
( tblname SYSNAME
, idxname SYSNAME
, idxdesc VARCHAR(1000)
, idxcol VARCHAR(1000)
)
CREATE TABLE #fraglist
( ObjectName SYSNAME
, ObjectId INT
, IndexName SYSNAME
, IndexId INT
, Lvl INT
, CountPages INT
, CountRows INT
, MinRecSize INT
, MaxRecSize INT
, AvgRecSize INT
, ForRecCount INT
, Extents INT
, ExtentSwitches INT
, AvgFreeBytes INT
, AvgPageDensity INT
, ScanDensity DECIMAL
, BestCount INT
, ActualCount INT
, LogicalFrag DECIMAL
, ExtentFrag DECIMAL
)
SET @dbname = db_name()
SET @sql = 'INSERT INTO #tableindex EXEC ' + @dbname + '..sp_index0'
EXEC (@sql)
IF (@objname IS NULL)
DELETE FROM #tableindex WHERE tblname = 'dtproperties'
ELSE
DELETE FROM #tableindex WHERE tblname != @objname
DECLARE cur_contig CURSOR READ_ONLY
FOR SELECT tblname, idxname
FROM #tableindex
OPEN cur_contig
FETCH NEXT FROM cur_contig
INTO @tblname, @idxname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @dbname
SET @sql = @sql +' DBCC SHOWCONTIG (''' + @tblname + ''','''
SET @sql = @sql + @idxname + ''') WITH TABLERESULTS'
INSERT INTO #fraglist
EXEC (@sql)
FETCH NEXT FROM cur_contig
INTO @tblname, @idxname
END
CLOSE cur_contig
DEALLOCATE cur_contig
IF (@value = 0)
SELECT objectname "TblName"
, indexname "IdxName"
, countpages "ScanPage"
, extents "ScanExtent"
, extentswitches "SwitchExtent"
, CASE WHEN extents = 0 THEN 0
ELSE CAST(CAST(countpages AS DEC)
/ CAST(extents AS DEC)
AS DEC(4,1))
END "Avg.Page/Extent"
, CAST(scandensity AS VARCHAR(6)) + '% ['
+ CAST(bestcount AS VARCHAR(10)) + ':'
+ CAST(actualcount AS VARCHAR(10)) + ']'
"ScanDensity[Best:Actual]"
, CAST(logicalfrag AS Varchar(3)) + '%' "LogicalScanFrag"
, CAST(extentfrag AS vARCHAR(3)) + '%' "ExtentScanFrag"
, avgfreebytes "Avg.FreeBytes/Page"
, CAST(avgpagedensity AS VARCHAR(3)) + '%' "Avg.PageDensity(Full)"
, ForRecCount "ForwardedRec."
FROM #fraglist
ELSE
SELECT objectname "TblName"
, indexname "IdxName"
, countpages "ScanPage"
, extents "ScanExtent"
, extentswitches "SwitchExtent"
, CASE WHEN extents = 0 THEN 0
ELSE CAST(CAST(countpages AS DEC)
/ CAST(extents AS DEC)
AS DEC(4,1))
END "Avg.Page/Extent"
, CAST(scandensity AS VARCHAR(6)) + '% ['
+ CAST(bestcount AS VARCHAR(10)) + ':'
+ CAST(actualcount AS VARCHAR(10)) + ']'
"ScanDensity[Best:Actual]"
, CAST(logicalfrag AS Varchar(3)) + '%' "LogicalScanFrag"
, CAST(extentfrag AS vARCHAR(3)) + '%' "ExtentScanFrag"
, avgfreebytes "Avg.FreeBytes/Page"
, CAST(avgpagedensity AS VARCHAR(3)) + '%' "Avg.PageDensity(Full)"
, ForRecCount "ForwardedRec."
FROM #fraglist
WHERE LogicalFrag >= @value
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
DROP TABLE #tableindex
DROP TABLE #fraglist
RETURN(0) --sp_showcontig0
SET NOCOUNT OFF
GO
GRANT EXECUTE ON sp_showcontig0 TO PUBLIC
GO
'Program > MSSQL' 카테고리의 다른 글
| MSSQL 파일그룹, 할당 용량, 현재 사용중인 용량, 사용 가능한 용량 조회 (0) | 2018.04.17 |
|---|---|
| MSSQL TABLE REBUILD (0) | 2018.04.17 |
| 동적쿼리로 테이블명 인자로 받기 (0) | 2018.01.23 |
| 연결된서버 사용하기 (linked server) (0) | 2017.12.24 |
| MSSQL DB 관리 쿼리들 (0) | 2017.12.20 |