/************************************************************************************************
** SQL Server 2000 DBA °ü¸®¿ë ½Ã½ºÅÛ ÇÁ·Î½ÃÀú »ý¼º
exec sp_table0
exec sp_column0 
exec sp_filegroup0
exec sp_index0
exec sp_constraint0
exec sp_server0
exec sp_dboption0
exec sp_getwaitstats0
exec sp_waitstats0
exec sp_freedisksize0
exec sp_filepageno0
exec sp_converthex0
exec sp_fnprvw0
exec sp_tracestart0
exec sp_tracestatus0
exec sp_tracestop0
exec sp_showcontig0
exec sp_umsstats0
exec sp_filestats0
exec sp_block0
exec sp_lock0
exec sp_trigger0
exec sp_spaceused0
exec sp_rowlength0
exec sp_object0
exec sp_cacheobject0
exec sp_login0
exec sp_user0
************************************************************************************************/

/************************************************************************************************
-- sp_table0
-- µðÆúÆ®·Î À¯Àú Å×À̺íÀ», ±×¿Ü ½Ã½ºÅÛ°ú ºä¸¦ º¸¿©ÁØ´Ù.
-- Tempdb¿¡¼­ ½ÇÇà½Ã ±³Âø »óÅ°¡ ¹ß»ýÇÑ´Ù.
-- ÆĶó¹ÌÅͷδ '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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  DECLARE @owner SYSNAME
  DECLARE @name SYSNAME
  DECLARE @oid INT
  DECLARE @type CHAR(2)     
  DECLARE @pages INT
  DECLARE @dbname SYSNAME
  DECLARE @dbsize BIGINT
  DECLARE @logsize BIGINT
  DECLARE @bytesperpage BIGINT
  DECLARE @pagesperMB BIGINT
  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
  , lob_filegroup SYSNAME NULL
  , rows BIGINT NULL
  , reserved BIGINT NULL
  , data BIGINT NULL
  , indexp BIGINT NULL
  , unused BIGINT 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
      /*******************************************************************/
        -- LOB Filegroup ̧̉
        UPDATE #return_tbl
        SET lob_filegroup = (select sf.groupname
                             from sysindexes si inner join sysfilegroups sf
                                                on si.groupid = sf.groupid
                             where si.indid = 255
                             and si.id = @oid
                            )        
        WHERE CURRENT OF cur_tbl
      
        -- #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 "TABLE", filegroup "FILEGROUP", '(n/a)' "FILEGROUP_TYPE"
         , lob_filegroup "LOB_FILEGROUP"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,rows),112),N'.00',N'') "ROW_COUNT"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,reserved * d.low / 1024),112),N'.00',N'') "RESERVED_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,data * d.low / 1024),112),N'.00',N'') "DATA_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,indexp * d.low / 1024),112),N'.00',N'') "INDEX_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,unused * d.low / 1024),112),N'.00',N'') "UNUSED_kb"
    FROM #return_tbl r, master.dbo.spt_values d
    WHERE d.number = 1
      AND d.type = 'E'
    ORDER BY owner, r.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 u.name, 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  


  RETURN(0) --sp_table0
GO

EXEC sp_MS_marksystemobject 'sp_table0'
GO

GRANT EXECUTE ON sp_table0 TO PUBLIC
GO


/************************************************************************************************
-- sp_column0 
-- ÇØ´ç DBÀÇ ¸ðµç Å×À̺í.Ä÷³À» º¸¿©ÁØ´Ù
************************************************************************************************/
USE MASTER
GO

IF OBJECT_ID('sp_column0') IS NOT NULL
   DROP PROCEDURE sp_column0
GO

CREATE PROC sp_column0    
AS  
  SET NOCOUNT ON  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
    DECLARE @own_tbl_nm SYSNAME
    DECLARE @ind_id SMALLINT
    DECLARE @i SMALLINT
    DECLARE @keys SYSNAME

    DECLARE @pk_keys TABLE
    ( tbl_nm SYSNAME
    , own_nm SYSNAME
    , col_nm SYSNAME
    )

    DECLARE cur_pk_keys CURSOR FAST_FORWARD
    FOR SELECT u.name + '.' + object_name(a.parent_obj) "own_tbl_nm"
            , b.indid "ind_id" 
        FROM sysobjects a 
        INNER JOIN sysindexes b 
            ON a.parent_obj = b.id 
            AND object_name(a.id) = b.name
        INNER JOIN dbo.sysusers u
            ON a.uid = u.uid
        WHERE a.xtype = 'PK'

    OPEN cur_pk_keys

    FETCH NEXT FROM cur_pk_keys
    INTO @own_tbl_nm, @ind_id

    WHILE(@@fetch_status = 0)
    BEGIN
        SET @i = 1

        SET @keys = index_col(@own_tbl_nm, @ind_id, @i)

        WHILE (@keys IS NOT NULL)
        BEGIN

            INSERT INTO @pk_keys 
            VALUES (parsename(@own_tbl_nm,1), parsename(@own_tbl_nm,2), @keys)            

            SET @i = @i + 1
            SET @keys = index_col(@own_tbl_nm, @ind_id, @i)
        END

        FETCH NEXT FROM cur_pk_keys
        INTO @own_tbl_nm, @ind_id
    END

    CLOSE cur_pk_keys
    DEALLOCATE cur_pk_keys

    SELECT u.name "OWNER", a.name "TABLE", b.name "COLUMN"
     , UPPER(
        CASE WHEN c.name IN ('nvarchar', 'nchar') 
                THEN c.name + '(' + CAST(b.length/2 AS VARCHAR(5)) + ')'
            WHEN c.name IN ('varchar', 'char', 'varbinary', 'binary') 
                THEN c.name + '(' + CAST(b.length AS VARCHAR(5)) + ')'
            WHEN c.name IN ('decimal', 'numeric') 
                THEN c.name + '(' + CAST(b.xprec AS VARCHAR(5)) + '.' + CAST(b.scale AS VARCHAR(5)) + ')'
            ELSE c.name
        END) "COLUMN_TYPE"
     , b.length "LENGTH_BT"
     , b.isnullable "IS_NULL"
     , CASE WHEN f.col_nm IS NULL THEN 0
            ELSE 1 
       END "IS_PRIMARYKEY"
     , CASE WHEN e.fkey IS NULL THEN 0
            ELSE 1
       END "IS_FOREIGNKEY"    
     , b.colstat "IS_IDENTITY"
	 , b.iscomputed "IS_COMPUTED"
     , b.colorder "COLUMN_ORDER"
    FROM dbo.sysobjects a
    INNER JOIN dbo.sysusers u
        ON a.uid = u.uid
    INNER JOIN dbo.syscolumns b
        ON a.id = b.id
    INNER JOIN dbo.systypes c
        ON b.xusertype = c.xusertype
    LEFT OUTER JOIN dbo.sysindexkeys d
        ON b.id = d.id
        AND b.colid = d.colid
        AND d.indid = 1
    LEFT OUTER JOIN dbo.sysforeignkeys e
        ON b.id = e.fkeyid
        AND b.colid = e.fkey
    LEFT OUTER JOIN @pk_keys f
        ON a.name = f.tbl_nm
        AND u.name = f.own_nm
        AND b.name = f.col_nm
    WHERE a.xtype = 'U'
    ORDER BY u.name, a.name, b.colorder
    
    RETURN(0) --sp_column0
GO

GRANT EXECUTE ON sp_column0 TO PUBLIC
GO


/************************************************************************************************
-- sp_filegroup0
-- ÇØ´ç DBÀÇ ÆÄÀÏ&ÆÄÀÏ ±×·ì Á¤º¸¸¦ º¸¿©ÁØ´Ù.
-- ÆĶó¹ÌÅÍ°¡ 'all' ÀÏ ¶§ Àüü »ç¿ëÀÚ DBÀÇ ÆÄÀÏ&ÆÄÀÏ ±×·ì Á¤º¸¸¦ º¸¿©ÁØ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('dbo.sp_filegroup0') IS NOT NULL
    DROP PROC sp_filegroup0
GO

  
CREATE PROC sp_filegroup0      
    @param NCHAR(3) = NULL  
AS  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
IF (@param IS NOT NULL AND @param != 'all')  
BEGIN    
RAISERROR('''@param'' parameter have to be NULL or ''all'' only.', 16, 1)  
  
RETURN (1)    
END    
  
DECLARE @db_name SYSNAME  
DECLARE @db_nm SYSNAME  
DECLARE @sql VARCHAR(2000)  
  
DECLARE @dblist TABLE  
( dbname SYSNAME  
)  
  
CREATE TABLE #result  
( [database] SYSNAME  
, [filegroup] SYSNAME  
, logical_file SYSNAME  
, physical_file SYSNAME  
, file_type VARCHAR(60)  
, is_readonly BIT  
, is_default BIT  
, [status] VARCHAR(60)  
, file_size_mb DECIMAL(15,2)  
, free_space_mb DECIMAL(15,2)  
, free_space_pct VARCHAR(10)  
, max_size VARCHAR(20)  
, growth VARCHAR(20)  
)  
  
IF (@param = 'all')  
    INSERT INTO @dblist  
    SELECT name  
    FROM master.dbo.sysdatabases  
    WHERE name not in ('master', 'model', 'msdb', 'tempdb')  
ELSE  
BEGIN  
    SET @db_name = db_name()  
  
    INSERT INTO @dblist  
    VALUES (@db_name)  
END  
  
DECLARE cur_filegroup CURSOR FAST_FORWARD  
FOR SELECT dbname  
    FROM @dblist  
  
OPEN cur_filegroup  
  
FETCH NEXT FROM cur_filegroup  
INTO @db_nm  
  
WHILE (@@FETCH_status = 0)  
BEGIN  
    SET @sql = ''  
    set @sql = @sql      
             + 'use ' + @db_nm + ' '      
             + 'SELECT ''' + @db_nm + ''' "DATABASE"'  
             + '     , ISNULL(fg.groupname, ''TransactiON Log'') "fg_nm" '  
             + '     , RTRIM(f.name) "lgc_f_nm" '  
             + '     , f.filename "phys_f_nm" '  
             + '     , CASE f.groupid WHEN 0 THEN ''LOG'' '  
             + '                      ELSE ''DATA'' '  
             + '       END "FILE_TYPE" '  
             + '     , CASE WHEN fg.status IN (8, 24) THEN 1 '  
             + '            ELSE 0 '  
             + '       END "IS_READONLY" '  
             + '     , CASE WHEN fg.status IN (16, 24) THEN 1 '  
             + '            ELSE 0 '  
             + '       END "IS_DEFAULT" '  
             + '     , CONVERT(VARCHAR(60),DATABASEPROPERTYEX(db_name(), ''Status'')) "STATUS" '  
             + '     , CONVERT(DEC(15,2), f.size*8/1000.0) "FILE_SIZE_mb" '  
             + '     , CONVERT(DEC(15,2), f.size*8/1000.0 - FILEPROPERTY(f.name, ''SpaceUsed'')*8/1000.0) "FREE_SPACE_mb" '  
             + '     , CONVERT(VARCHAR(8) '  
             + '              , CONVERT( DEC(5,2) '  
             + '                         , ( f.size*8/1000.0 - FILEPROPERTY(f.name, N''SpaceUsed'')*8/1000.0 '  
             + '                           ) / (f.size*8/1000.0) * 100 '  
             + '                         ) '  
             + '              ) + ''%'' "FREE_SPACE_pct" '  
             + '     , case f.maxsize when -1 then N''Unlimited''    
                           else REPLACE(CONVERT(VARCHAR(15),CONVERT(MONEY,f.maxsize * 8),112),''.00'','''')  + '' KB''   
                       end "MAX_SIZE" '   
             + '     , case f.status & 0x100000 when 0x100000   
                           then REPLACE(CONVERT(VARCHAR(15),CONVERT(MONEY,f.growth),112),''.00'','''')  + ''%''    
                           else REPLACE(CONVERT(VARCHAR(15),CONVERT(MONEY,f.growth * 8),112),''.00'','''')  + '' KB''    
                       end "GROWTH" '  
             + 'FROM dbo.sysfiles f '  
             + 'LEFT OUTER JOIN dbo.sysfilegroups fg '  
             + 'ON f.groupid = fg.groupid '  
      
    INSERT INTO #result exec(@sql)  
  
    FETCH NEXT FROM cur_filegroup  
    INTO @db_nm  
END  
  
SELECT [DATABASE], [FILEGROUP], LOGICAL_FILE, PHYSICAL_FILE, FILE_TYPE, IS_READONLY, IS_DEFAULT  
     , MAX_SIZE "MAX_SIZE", GROWTH "GROWTH", [STATUS], FILE_SIZE_mb, FREE_SPACE_mb, FREE_SPACE_pct  
FROM #result  
ORDER BY [DATABASE]  
       , CASE [FILEGROUP] WHEN 'PRIMARY' THEN '0'  
                          WHEN 'TransactiON Log' THEN 'zzzzzzzzzzzzzz'  
                          ELSE [FILEGROUP]  
         END  
  
CLOSE cur_filegroup  
DEALLOCATE cur_filegroup  
  
RETURN (0) --sp_filegroup0 'all'  
GO

GRANT EXECUTE ON sp_filegroup0 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
                            )  
    
  INSERT @probclients   
  SELECT spid, ecid, blocked, waittype, dbid  
  ,      CASE WHEN CONVERT(VARCHAR(128), hostname) = 'PSSDIAG' THEN 1   
              WHEN CONVERT(VARCHAR(128), hostname) = 'SQLDIAG' THEN 1
              ELSE 0   
         END  
  FROM master.dbo.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.dbo.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  
             )  
  -- óÀ½ °á°ú ³¡  
  
  -- µÎ¹ø° °á°ú ½ÃÀÛ  sp_block0
  DECLARE @spid INT  
  DECLARE @msg VARCHAR(300)  
    
  CREATE TABLE #infoevent( id INT IDENTITY  
                         , spid INT  
                         , eventtype VARCHAR(600)  
                         , parameters INT  
                         , eventinfo VARCHAR(600)  
                         )  
  
  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(50), @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  
  -- µÎ¹ø° °á°ú ³¡  
  
  -- ¼¼¹ø° °á°ú ½ÃÀÛ  
  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+'.dbo.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   
  -- ¼¼¹ø° °á°ú ³¡  

  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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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


GO
  
GRANT EXECUTE ON sp_server0 TO PUBLIC
GO


/************************************************************************************************
-- sp_dboption0
-- µ¥ÀÌÅͺ£À̽º ¼³Á¤ ¿É¼ÇÀ» º¸¿©ÁØ´Ù.
************************************************************************************************/
USE MASTER
GO

IF OBJECT_ID('sp_dboption0') IS NOT NULL
   DROP PROCEDURE sp_dboption0
GO

CREATE PROC sp_dboption0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @db_nm SYSNAME
SET @db_nm = db_name()

CREATE TABLE #all_set (list VARCHAR(50))
CREATE TABLE #db_set (list VARCHAR(50))

INSERT INTO #all_set EXEC master..sp_dboption
INSERT INTO #db_set EXEC master..sp_dboptiON @db_nm

SELECT a.list "Options" , CASE WHEN b.list is NULL THEN 0
                    ELSE 1
               END "Set"

FROM #all_set a LEFT OUTER JOIN #db_set b
                ON a.list = b.list
  
RETURN(0) --sp_dboption0
GO
  
GRANT EXECUTE ON sp_dboption0 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
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

SELECT u.name "OWNER", o.name "TABLE", i.name "INDEX"
   , CONVERT(VARCHAR(210), CASE WHEN (i.status & 16)<>0 THEN 'Clustered' 
                                ELSE 'Nonclustered'
                           END
            ) "INDEX_TYPE"                 
   , LEFT(  ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 1) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 1, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 2) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 2, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 3) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 3, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 4) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 41, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 5) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 51, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 6) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 6, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 7) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 7, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 8) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 8, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,''),
          LEN(
          ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 1) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 1, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 2) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 2, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 3) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 3, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 4) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 4, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 5) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 5, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 6) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 6, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 7) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 7, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          + ISNULL(INDEX_COL(u.name + '.' + o.name,indid, 8) 
                +CASE WHEN INDEXKEY_PROPERTY(o.id, indid, 8, 'isdescending') = 1 THEN '(-), '
                      ELSE ', ' END
                ,'')
          ) - 1) "INDEX_COLUMNS"
   , f.groupname "FILEGROUP"
   , '(n/a)' "FILEGROUP_TYPE"
   , CASE WHEN (i.status & 2048)<>0 THEN 1
          ELSE 0 
     END "IS_PRIMARYKEY"
   , CASE WHEN (i.status & 2)<>0 THEN 1
          ELSE 0 
     END "IS_UNIQUE"
   , '(n/a)' "INCLUDED_COLUMNS"
FROM sysindexes i INNER JOIN sysobjects o
                ON i.id = o.id
                INNER JOIN sysusers u
                ON o.uid = u.uid
                INNER JOIN sysfilegroups f
                ON i.groupid = f.groupid
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

RETURN(0) --sp_index0
GO 
                       
EXEC sp_MS_marksystemobject N'dbo.sp_index0'
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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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


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 
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT su.name "OWNER", object_name(so.parent_obj) "TABLE", so.name "CONSTRAINT"
     , CASE so.xtype WHEN 'D' THEN 'DEFAULT_CONSTRAINT'
                     WHEN 'C' THEN 'CHECK_CONSTRAINT'
                     WHEN 'F' THEN 'FOREIGN_KEY_CONSTRAINT'
       END "CONSTRAINT_TYPE"
     , ISNULL(sc.text, '(n/a)') "CONSTRAINT_DEFINITION"
     , CASE WHEN so.xtype = 'D' THEN '(n/a)'
            ELSE cast(ObjectProperty(so.id, 'CnstIsDisabled') as VARCHAR(5))
       END "IS_DISABLED"
     , CASE WHEN so.xtype = 'F' THEN sq.fcols
            WHEN so.xtype = 'D' THEN col_name(so.parent_obj, so.info)
            ELSE '(n/a)'
       END "PARENT_COLUMNS"
     , CASE WHEN so.xtype <> 'F' THEN '(n/a)'
            ELSE sq.rtbl_nm 
       end"REFERENCES_TABLE"
     , CASE WHEN so.xtype <> 'F' THEN '(n/a)'
            ELSE sq.rcols 
       END "REFERENCES_COLUMNS"
     , CASE WHEN so.xtype <> 'F' THEN '(n/a)'
            ELSE CASE WHEN ObjectProperty(so.id, 'CnstIsDeleteCascade') = 0 THEN 'NO_ACTION'
                      ELSE 'CASCADE'
                 END
       END "DELETE_ACTION"
     , CASE WHEN so.xtype <> 'F' THEN '(n/a)'
            ELSE CASE WHEN ObjectProperty(so.id, 'CnstIsupdateCascade') = 0 THEN 'NO_ACTION'
                      ELSE 'CASCADE'
                 END
       END "UPDATE_ACTION"
FROM sysobjects so
INNER JOIN sysusers su
    ON so.uid = su.uid
LEFT OUTER JOIN syscomments sc
    ON so.id = sc.id
LEFT OUTER JOIN (SELECT constid
                      , user_name(ObjectProperty(rkeyid,'ownerid')) + '.' + object_name(rkeyid) "rtbl_nm"
                      , replace( ISNULL(col_name(fkeyid, fkey1),'-') + ', ' + ISNULL(col_name(fkeyid, fkey2),'-') + ', '
                               + ISNULL(col_name(fkeyid, fkey3),'-') + ', ' + ISNULL(col_name(fkeyid, fkey4),'-') + ', '
                               + ISNULL(col_name(fkeyid, fkey5),'-') + ', ' + ISNULL(col_name(fkeyid, fkey6),'-') + ', '
                               + ISNULL(col_name(fkeyid, fkey7),'-') + ', ' + ISNULL(col_name(fkeyid, fkey8),'-') + ', '
                               + ISNULL(col_name(fkeyid, fkey9),'-') + ', ' + ISNULL(col_name(fkeyid, fkey10),'-') + ', '
                               + ISNULL(col_name(fkeyid, fkey11),'-') + ', ' + ISNULL(col_name(fkeyid, fkey12),'-')
                               , ', -', '') "fcols"
                      , replace( ISNULL(col_name(rkeyid, rkey1),'-') + ', ' + ISNULL(col_name(rkeyid, rkey2),'-') + ', '
                               + ISNULL(col_name(rkeyid, rkey3),'-') + ', ' + ISNULL(col_name(rkeyid, rkey4),'-') + ', '
                               + ISNULL(col_name(rkeyid, rkey5),'-') + ', ' + ISNULL(col_name(rkeyid, rkey6),'-') + ', '
                               + ISNULL(col_name(rkeyid, rkey7),'-') + ', ' + ISNULL(col_name(rkeyid, rkey8),'-') + ', '
                               + ISNULL(col_name(rkeyid, rkey9),'-') + ', ' + ISNULL(col_name(rkeyid, rkey10),'-') + ', '
                               + ISNULL(col_name(rkeyid, rkey11),'-') + ', ' + ISNULL(col_name(rkeyid, rkey12),'-')
                               , ', -', '') "rcols"
                 FROM sysreferences
                ) sq
    ON so.id = sq.constid
WHERE so.xtype IN ('C', 'D', 'F')
ORDER BY [owner], [table], [constraint];

PRINT 'This Procedure provide all constraints except Primary Key and Unique.'
PRINT 'You can see Primary Key or Unique constraints using dbo.sp_table0 or dbo.sp_column0.'

RETURN (0) --sp_constraint0
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 
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT a.name "TRIGGER"
     , 'OBJECT_OR_COLUMN' "TRIGFER_TYPE"
     , c.name + '.' + b.name "TRIGGERED_OBJECT"
     , ObjectProperty(a.id, 'ExecIsUpdateTrigger') "IS_UPDATE"
     , ObjectProperty(a.id, 'ExecIsDeleteTrigger') "IS_DELETE"
     , ObjectProperty(a.id, 'ExecIsInsertTrigger') "IS_INSERT"
     , ObjectProperty(a.id, 'ExecIsAfterTrigger') "AFTER_TRG"
     , ObjectProperty(a.id, 'ExecIsInsteadOfTrigger') "INSTEAD_OF_TRG"
     , ObjectProperty(a.id, 'ExecIsTriggerDisabled') "IS_DISABLED"
FROM sysobjects a
LEFT JOIN sysobjects b
ON a.parent_obj = b.id
INNER JOIN sysusers c
ON b.uid = c.uid
WHERE a.type = 'TR' 

RETURN(0) --sp_trigger0
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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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

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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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


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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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


  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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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 "DISK"
     , REPLACE(CONVERT(VARCHAR(10),CONVERT(MONEY,totalsize),112),'.00','') "DISK_SIZE_mb"
     , REPLACE(CONVERT(VARCHAR(10),CONVERT(MONEY,freesize),112),'.00','') "FREE_SIZE_mb"
     , CONVERT(VARCHAR(10),(CAST(CAST(freesize as DEC(10,2)) / CAST(totalsize AS DEC(10,2)) * 100 AS DEC(10,2)))) + '%'
       "FREE_SIZE_pct"
  FROM #drives

  RETURN(0) --sp_freedisksize0
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
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  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 

GO


EXEC sp_MS_marksystemobject 'sp_object0'
GO
  
GRANT EXECUTE ON sp_object0 TO PUBLIC
GO


/************************************************************************************************
-- sp_showcontig0
-- À妽º Á¶°¢È­ Á¤º¸¸¦ Àüü¿Í ±âÁØ°ªÀ» ±âÁØÀ¸·Î º¸¿©ÁØ´Ù.
-- ù¹ø° ÆĶó¹ÌÅÍ´Â Á¶°¢È­ ¼öÄ¡ ±âÁØÀ¸·Î ÇÊÅ͸µ.
-- µÎ¹ø° ÆĶó¹ÌÅÍ´Â fast ¿É¼Ç Àû¿ë ¿©ºÎ(IN 'fast'(default) of 'all')
-- exec sp_showcontig0; exec sp_showcontig0 10; exec sp_showcontig0 0, 'all';
************************************************************************************************/
USE MASTER
GO 

IF OBJECT_ID('sp_showcontig0') IS NOT NULL
   DROP PROCEDURE sp_showcontig0
GO
--DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
CREATE PROC sp_showcontig0 
  @value decimal(6,2) = 0.0
, @fast char(4) = 'fast'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF (@fast <> 'fast' AND @fast <> 'all')
BEGIN
    PRINT '!! ''@fast'' parameter have to be ''fast'' or ''all'' only.'
    RETURN (-1)
END

DECLARE @dbname SYSNAME
DECLARE @sql VARCHAR(300)

CREATE TABLE #fraglist
( ObjectName SYSNAME NULL
, ObjectId INT NULL
, IndexName SYSNAME NULL
, IndexId INT NULL
, Lvl INT NULL
, CountPages INT NULL
, CountRows INT NULL
, MinRecSize INT NULL
, MaxRecSize INT NULL
, AvgRecSize INT NULL
, ForRecCount INT NULL
, Extents INT NULL
, ExtentSwitches INT NULL
, AvgFreeBytes INT NULL
, AvgPageDensity DECIMAL(6,2) NULL
, ScanDensity DECIMAL NULL
, BestCount INT NULL
, ActualCount INT NULL
, LogicalFrag DECIMAL(6,2) NULL
, ExtentFrag DECIMAL(6,2) NULL
)

SET @dbname = db_name()

SET @sql = ''
SET @sql = @sql + 'USE ' + @dbname
SET @sql = @sql +' DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES '
IF (@fast = 'fast')
  SET @sql = @sql + ', FAST'

INSERT INTO #fraglist 
EXEC (@sql)

SELECT c.name "OWNER"
   , a.objectname "TABLE"
   , a.indexname "INDEX"
   , a.indexid "INDEX_ID"
   , CASE WHEN a.indexid = 0 THEN 'HEAP' 
          WHEN a.indexid = 1 THEN 'CLUSTERED INDEX'
          WHEN a.indexid BETWEEN 2 AND 254 THEN 'NONCLUSTERED INDEX'
          WHEN a.indexid = 255 THEN 'LOB DATA'
          ELSE '!! Error'
     END "INDEX_TYPE"
   , a.countpages "ScanPage"
   , a.extents "ScanExtent"
   , a.extentswitches "SwitchExtent"
   , CASE WHEN a.extents = 0 THEN 0.00
          ELSE CAST(CAST(a.countpages AS DEC) / CAST(a.extents AS DEC) AS DEC(6,2)) 
     END "Avg.Page/Extent"
   , CAST(a.scandensity AS VARCHAR(6)) + '% ['
     + CAST(a.bestcount AS VARCHAR(10)) + ':'
     + CAST(a.actualcount AS VARCHAR(10)) + ']'
     "ScanDensity[Best:Actual]"
   , CAST(a.logicalfrag AS DECIMAL(6,2)) "LogicalScanFreg"
   , CAST(a.extentfrag AS DECIMAL(6,2)) "ExtentScanFrag"
   , a.avgfreebytes "Avg.FreeBytes/Page"
   , CAST(a.avgpagedensity AS DECIMAL(6,2)) "Avg.PageDensity(Full)"
   , a.ForRecCount "ForwardedRec."
FROM #fraglist a 
INNER JOIN dbo.sysobjects b
  ON a.objectid = b.id
  AND b.xtype IN ('U','V')
INNER JOIN dbo.sysusers c
  ON b.uid = c.uid
WHERE LogicalFrag >= @value
--AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
--AND IndexId <> 255
AND objectname <> 'dtproperties'
ORDER BY c.name, a.objectname

RETURN(0) --sp_showcontig0
GO

EXEC sp_MS_marksystemobject 'sp_showcontig0'
GO

GRANT EXECUTE ON sp_showcontig0 TO PUBLIC
GO


/************************************************************************************************
-- sp_getwaitstats0
-- sp_waitstats0¿¡¼­ ¼öÁýÇÑ ´ë±â ÀÚ¿ø Á¤º¸¸¦ Ãâ·ÂÇÑ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID(N'dbo.sp_getwaitstats0') IS NOT NULL
    DROP PROC sp_getwaitstats0
GO

CREATE PROC sp_getwaitstats0 
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- ÀÌ ÇÁ·Î½ÃÀú´Â SQL Server DBA °¡À̵忡¼­ ¾ð±ÞµÈ WAIT ÃßÀû Á¡°Ë ºÎºÐÀ» »ç¿ëÇß´Ù.

DECLARE @now DATETIME, @totalwait NUMERIC(20,1), @totalresourcewait NUMERIC(20,1)
      , @endtime DATETIME, @begintime DATETIME, @totalsignalwait NUMERIC(20,1)
      , @hr int, @MIN int, @sec int

SELECT @now=MAX(now), @begintime=MIN(now), @endtime=MAX(now)
FROM tempdb.dbo.waitstats0 
WHERE [wait type] = 'Total'

-- waitfor, sleep, resource_queue ´ë±âÀ¯ÇüÀº Àüü ´ë±â½Ã°£¿¡¼­ Á¦¿Ü.
SELECT @totalwait = sum([wait time]) + 1, @totalsignalwait = sum([signal wait time]) + 1 
FROM tempdb.dbo.waitstats0
WHERE [wait type] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total', '### TOTAL ###') 
AND now = @now

SET @totalresourcewait = 1 + @totalwait - @totalsignalwait

-- º¸Á¤µÈ ´ë±â½Ã°£ ÃѰ踦 »ðÀÔÇÏ°í, ÆÛ¼¾Æ®¿¡ µû¶ó ¿ª¼øÀ¸·Î Á¤·Ä.
DELETE tempdb.dbo.waitstats0 
WHERE [wait type] = '### TOTAL ###' 
AND now = @now

INSERT INTO tempdb.dbo.waitstats0 
SELECT '### TOTAL ###', 0, @totalwait, @totalsignalwait, @now

SELECT N'Start_Time' = @begintime, N'End_Time' = @endtime
     , N'Duration (hh:mm:ss:ms)' = convert(NVARCHAR(50), @endtime - @begintime, 14)

SELECT [wait type] "Wait_Type"
     , requests "Wating_Tasks_Count"
     , [wait time] "Total_Wait_ms"
     , cast (100 * [wait time] / @totalwait AS NUMERIC(20,1)) "Total_Wait_pct"
     , [wait time] - [signal wait time] "Resource_Wait_ms"
     , cast (100 * ([wait time] - [signal wait time]) / @totalresourcewait AS NUMERIC(20,1)) "Resource_Wait_pct"
     , [signal wait time] "Signal_Wait_ms"
     , cast (100 * [signal wait time] / @totalsignalwait AS NUMERIC(20,1)) "Signal_Wait_pct"
FROM tempdb.dbo.waitstats0
WHERE [wait type] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
AND now = @now 
ORDER BY CASE WHEN [wait type] = '### TOTAL ###' THEN 1
              ELSE 2
         END ASC
       , "Total_Wait_pct" DESC

RETURN (0) -- sp_getwaitstats0
GO


/************************************************************************************************
-- sp_waitstats0
-- ´ë±â ÀÚ¿ø Á¤º¸¸¦ ¼öÁýÇÏ°í sp_getwaitstats0¸¦ ÅëÇØ Ãâ·ÂÇÑ´Ù.
-- exec dbo.sp_waitstats0; exec sp_waitstats0 4, 10, 's';
************************************************************************************************/
USE master
GO

IF OBJECT_ID(N'dbo.sp_waitstats0') IS NOT NULL
    DROP PROC sp_waitstats0;
GO

CREATE PROC sp_waitstats0 
    ( @num_samples int = 4
    , @delay_interval int = 30
    , @delay_type nvarchar(10)='seconds')
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- ÀÌ ÇÁ·Î½ÃÀú´Â SQL Server DBA °¡À̵忡¼­ ¾ð±ÞµÈ WAIT ÃßÀû Á¡°Ë ºÎºÐÀ» »ç¿ëÇß´Ù.
-- @num_samples ¸Å°³º¯¼ö´Â ´ë±â½Ã°£ Åë°è°ªÀ» Áý°èÇϴ Ƚ¼ö; ±âº»°ªÀº 10¹øÀ¸·Î ¼³Á¤
-- @delay_interval ¸Å°³º¯¼ö´Â Áö¿¬°£°Ý;ºÐ ¶Ç´Â ÃÊ ´ÜÀ§·Î ÁöÁ¤°¡´É; ±âº»°ªÀº 1 ºÐ
-- @delay_type ¸Å°³º¯¼ö´Â Áö¿¬°£°Ý¿¡ ´ëÇÑ ½Ã°£´ÜÀ§¸¦ ÁöÁ¤. "minutes" ³ª "seconds"À» ÁöÁ¤
-- waitstats0 Å×À̺íÀÌ Á¸ÀçÇÏÁö ¾ÊÀ¸¸é »ý¼ºÇÏ°í, Á¸ÀçÇϸé TRUNCATE

IF NOT EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = 'waitstats0')
   CREATE TABLE tempdb.dbo.waitstats0 
   ( [wait type] VARCHAR(80)
   , [requests] NUMERIC(20,1)
   , [wait time] NUMERIC (20,1)
   , [signal wait time] NUMERIC(20,1)
   , [now] DATETIME default getdate()
   )
ELSE 
   TRUNCATE TABLE tempdb.dbo.waitstats0

DBCC SQLPERF (waitstats, clear) -- Clear out waitstats0.

DECLARE @i INT, @delay VARCHAR(8), @dt VARCHAR(3), @now DATETIME, @totalwait NUMERIC(20,1)
      , @endtime DATETIME, @begintime DATETIME, @hr INT, @min INT, @sec INT

SET @i = 1

SELECT @dt = case lower(@delay_type) WHEN 'minutes' THEN 'm'
                                    WHEN 'minute' THEN 'm'
                                    WHEN 'min' THEN 'm'
                                    WHEN 'mm' THEN 'm'
                                    WHEN 'mi' THEN 'm'
                                    WHEN 'm' THEN 'm'
                                    WHEN 'seconds' THEN 's'
                                    WHEN 'second' THEN 's'
                                    WHEN 'sec' THEN 's'
                                    WHEN 'ss' THEN 's'
                                    WHEN 's' THEN 's'
                                    ELSE @delay_type
             END

IF @dt NOT IN ('s','m')
BEGIN
   PRINT '!! Please supply delay type e.g. seconds or minutes.'
   RETURN
END

IF @dt = 's'
BEGIN
   SELECT @sec = @delay_interval % 60
   SELECT @min = cast((@delay_interval / 60) AS INT)
   SELECT @hr = cast((@min / 60) AS INT)
   SELECT @min = @min % 60
END

IF @dt = 'm'
BEGIN
   SELECT @sec = 0
   SELECT @min = @delay_interval % 60
   SELECT @hr = cast((@delay_interval / 60) AS INT)
END

SELECT @delay= right('0'+ convert(VARCHAR(2),@hr),2) + ':' +
     + right('0'+convert(VARCHAR(2),@min),2) + ':' +
     + right('0'+convert(VARCHAR(2),@sec),2)

IF @hr > 23 or @min > 59 or @sec > 59
BEGIN
   SELECT 'hh:mm:ss delay time cannot > 23:59:59'
   SELECT 'delay interval and type: ' + convert (VARCHAR(10),@delay_interval) + ','
        + @delay_type + ' converts to ' + @delay
   RETURN
END

WHILE (@i <= @num_samples)
BEGIN
      INSERT INTO tempdb.dbo.waitstats0 ([wait type], requests, [wait time],[signal wait time])
      EXEC ('DBCC SQLPERF(waitstats)')
      SET @i = @i + 1
      WAITFOR DELAY @delay
END

-- ´ë±â½Ã°£Á¤º¸¿¡ ´ëÇÑ º¸°í¼­¸¦ »ý¼º.
EXEC sp_getwaitstats0

RETURN (0) -- sp_waitstats0 4, 10, 's'
GO


/************************************************************************************************
-- sp_fnprvw0
-- ÇØ´ç DBÀÇ ¸ðµç ÇÔ¼ö/ÇÁ·Î½ÃÀú/ºä¿¡ ´ëÇÑ Á¤º¸¸¦ Ãâ·ÂÇÑ´Ù.
************************************************************************************************/
USE MASTER
GO

IF OBJECT_ID('sp_fnprvw0') IS NOT NULL
   DROP PROCEDURE sp_fnprvw0
GO

CREATE PROC sp_fnprvw0 
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT su.name "OWNER", so.name "OBJECT"
     , CASE so.xtype WHEN 'FN' THEN 'SQL_SCALAR_FUNCTION'
                     WHEN 'IF' THEN 'SQL_INLINE_TABLE_VALUED_FUNCTION'
                     WHEN 'TF' THEN 'SQL_TABLE_VALUED_FUNCTION'
                     WHEN 'V' THEN 'VIEW'
                     WHEN 'P' THEN 'SQL_STORED_PROCEDURE'
       END "OBJECT_TYPE"
     , so.crdate "CREATE_DATE", so.refdate "MODIFY_DATE"
     , CAST(sc.text AS varchar(800)) "DEFINITION"
FROM dbo.sysobjects so
INNER JOIN dbo.syscomments sc
  ON so.id = sc.id
INNER JOIN dbo.sysusers su
  ON so.uid = su.uid
WHERE so.xtype in ('FN', 'IF', 'TF', 'V', 'P')
AND so.name not like 'dt_%'
AND so.name not like 'sys%'
ORDER BY su.name, so.name

RETURN(0) -- sp_fnprvw0 
GO


EXEC sp_MS_marksystemobject 'sp_fnprvw0'
GO
  
GRANT EXECUTE ON sp_fnprvw0 TO PUBLIC
GO


/************************************************************************************************
-- sp_tracestart0
-- ÃßÀûÀ» ½ÇÇà½ÃÅ°°í, ÆÄÀÏÀº C:\ ¹Ø¿¡ »ý¼ºµÈ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_tracestart0') IS NOT NULL
    DROP PROCEDURE sp_tracestart0
GO

CREATE PROC sp_tracestart0
  @TraceFileName sysname = NULL
, @TraceName sysname = 'trace_'
, @Options int = 2                  -- TRACE_FILE_ROLLOVER
, @MaxFileSize bigint = 50
, @StopTime datetime = NULL
, @Events varchar(300) = '10,12,25,37,41,43,166,92,93,94,95'     -- À̺¥Æ® ¼±ÅÃ
, @Cols varchar(300) = '1,3,22,18,16,17,13,10,8,6,11,9,12,14,15,21' -- Ä÷³ ¼±ÅÃ
, @IncludeFilter sysname = NULL
, @ExcludeFilter sysname = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- º¯¼ö ¼±¾ð
DECLARE @TraceId INT
DECLARE @On BIT 
DECLARE @rc INT 
DECLARE @Events_Tbl TABLE(seq int not null identity, event int)
DECLARE @Cols_Tbl TABLE(seq int not null identity, col int)
DECLARE @iEvent INT, @iCol INT

SET @On = 1

-- À̺¥Æ®¿Í Ä÷³À» È®ÀÎÇÑ´Ù.
IF @Events IS NULL or @Cols IS NULL 
BEGIN 
 PRINT 'No Events or Coloumns.'
 RETURN (-1)
END 

-- À̺¥Æ®¿Í Ä÷³ÀÇ Å×ÀÌºí º¯¼ö ÀúÀå
WHILE charindex(',', @Events) > 0
BEGIN
    INSERT INTO @Events_Tbl VALUES(REPLACE(LEFT(@Events, charindex(',', @Events)),',',''))
    SET @Events = RIGHT(@Events, len(@Events) - charindex(',', @Events))
END
INSERT INTO @Events_Tbl VALUES (REPLACE(@Events,',',''))

WHILE charindex(',', @Cols) > 0
BEGIN
    INSERT INTO @Cols_Tbl VALUES(REPLACE(LEFT(@Cols, charindex(',', @Cols)),',',''))
    SET @Cols = RIGHT(@Cols, len(@Cols) - charindex(',', @Cols))
END
INSERT INTO @Cols_Tbl VALUES (REPLACE(@Cols,',',''))

-- ÆÄÀÏ°æ·Î¿Í ÆÄÀϸíÀ» ¼³Á¤ÇÑ´Ù.
IF @TraceFileName IS NULL          
 SET @TraceFileName = 'c:\trc-' + CONVERT(CHAR(6), GETDATE(), 12)+ '-' + REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':','')

-- ÃßÀû Å¥¸¦ ¸¸µç´Ù
EXEC @rc = sp_trace_create @TraceId OUT, @Options, @TraceFileName, @MaxFileSize, @StopTime

IF @rc <> 0 
BEGIN
 PRINT 'Trace not started(' + CAST(@rc as varchar(10)) + ').'
 PRINT CASE @rc WHEN 1 THEN '¾Ë ¼ö ¾ø´Â ¿À·ùÀÔ´Ï´Ù.'
                WHEN 10 THEN 'À߸øµÈ ¿É¼ÇÀÔ´Ï´Ù. ÁöÁ¤ÇÑ ¿É¼ÇÀÌ È£È¯µÇÁö ¾ÊÀ¸¸é ¹ÝȯµË´Ï´Ù.'
                WHEN 12 THEN '°æ·Î³ª ÆÄÀÏÀÌ ¸¸µé¾î ÁöÁö ¾Ê¾Ò½À´Ï´Ù.'
                WHEN 13 THEN '¸Þ¸ð¸®°¡ ºÎÁ·ÇÕ´Ï´Ù. ÁöÁ¤ÇÑ µ¿ÀÛÀ» ¼öÇàÇÒ ¸Þ¸ð¸®°¡ ÃæºÐÇÏÁö ¾ÊÀ¸¸é ¹ÝȯµË´Ï´Ù.'
                WHEN 14 THEN 'À߸øµÈ ÁßÁö ½Ã°£ÀÔ´Ï´Ù. ÁöÁ¤ÇÑ ÁßÁö ½Ã°£ÀÌ ÀÌ¹Ì Áö³­ °æ¿ì ¹ÝȯÇÕ´Ï´Ù.'
                WHEN 15 THEN 'À߸øµÈ ¸Å°³ º¯¼öÀÔ´Ï´Ù. »ç¿ëÀÚ°¡ ȣȯµÇÁö ¾Ê´Â ¸Å°³ º¯¼ö¸¦ Á¦°øÇÏ¸é ¹ÝȯµË´Ï´Ù.'
                ELSE 'Books Online¿¡¼­ sp_trace_create¸¦ ÂüÁ¶ÇϽʽÿÀ.'
       END
 RETURN (@rc)
END 

-- ÃßÀûÇÒ À̺¥Æ® Ŭ·¡½ºµé°ú Ä÷³µéÀ» ÁöÁ¤ÇÑ´Ù
DECLARE cur_EventsCols CURSOR FAST_FORWARD
FOR SELECT a.event, b.col
    FROM @Events_Tbl a CROSS JOIN @Cols_Tbl b
    ORDER BY b.seq, a.seq

OPEN cur_EventsCols

FETCH NEXT FROM cur_EventsCols
INTO @iEvent, @iCol

WHILE (@@fetch_status = 0)
BEGIN
    EXEC sp_trace_setevent @TraceId, @iEvent, @iCol, @On
    
    FETCH NEXT FROM cur_EventsCols
    INTO @iEvent, @iCol
END

CLOSE cur_EventsCols
DEALLOCATE cur_EventsCols

-- ÇÊÅ͸¦ ¼³Á¤ÇÑ´Ù
EXEC sp_trace_setfilter @TraceId, 10, 0, 7, N'%SQL Profiler%'
-- EXEC sp_trace_setfilter @TraceId,  1, 0, 7, N'%sp_trace%' (Çã¿ë½Ã ReadTrace ¿¡·¯ ¹ß»ý!! ¿ÖÁö??)
EXEC sp_trace_setfilter @TraceId,  8, 0, 7, N'%PSSDiag%'

IF @IncludeFilter IS NOT NULL 
   EXEC sp_trace_setfilter @TraceId, 1, 0, 6, @IncludeFilter

IF @ExcludeFilter IS NOT NULL 
   EXEC sp_trace_setfilter @TraceId, 1, 0, 7, @ExcludeFilter

-- ÃßÀûÀ» È°¼ºÈ­ÇÑ´Ù
EXEC sp_trace_setstatus @TraceId, 1

-- ÃßÀûÀ» ±â·ÏÇÑ´Ù. (Å×ÀÌºí »ç¿ë)
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL 
BEGIN
    CREATE TABLE tempdb..TraceQueueList 
    ( TraceID INT
    , TraceName VARCHAR(30)
    , TraceFile SYSNAME
    , TraceStatus VARCHAR(30)
    )
END
ELSE
BEGIN
    UPDATE tempdb..TraceQueueList 
    SET TraceStatus = 'Running'
    WHERE TraceStatus = 'Started'
END

INSERT tempdb..TraceQueueList 
VALUES( @TraceId, @TraceName+CAST(@TraceId AS VARCHAR(5)), @TraceFileName, 'Started')

SELECT * FROM tempdb..TraceQueueList 

RETURN (0)  -- sp_tracestart0
GO

GRANT EXECUTE ON sp_tracestart0 TO PUBLIC
GO


/************************************************************************************************
-- sp_tracestatus0
-- ½ÇÇàµÇ°í ÀÖ´Â ÃßÀû ³»¿ªÀ» º¸¿©ÁØ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_tracestatus0') IS NOT NULL
   DROP PROC sp_tracestatus0
GO

CREATE PROC sp_tracestatus0 
  @TraceID INT = 0
AS 
SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..TraceQueueList') IS NULL
BEGIN
    PRINT 'No active traces. Not exists tempdb..TraceQueueList table.'
END
ELSE 
    SELECT *
    FROM tempdb..TraceQueueList 

RETURN (0) -- sp_tracestatus0 
GO 

GRANT EXECUTE ON sp_tracestatus0 TO PUBLIC
GO


/************************************************************************************************
-- sp_tracestop0
-- sp_tracestart0¿¡ ÀÇÇØ ½ÇÇàµÇ°í ÀÖ´Â ÃßÀûÀ» ÁßÁö ½ÃŲ´Ù.
-- @TraceID°¡ ¾øÀ¸¸é ÀüÁ¦¸¦, ÀÖÀ¸¸é ÇØ´ç TraceID°Ç¸¸ ÁßÁö½ÃŲ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_tracestop0') IS NOT NULL
   DROP PROC sp_tracestop0
GO

CREATE PROC sp_tracestop0
    @TraceID INT = 0
AS 
SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- º¯¼ö¸¦ ¼±¾ðÇÑ´Ù 
DECLARE @TraceFileName SYSNAME
DECLARE @iTraceID INT

-- ÃßÀû ¸ñ·ÏÀ» È®ÀÎÇÏ¿©, ÃßÀûÀ» ÁßÁöÇÑ´Ù 
IF OBJECT_ID('tempdb..TraceQueueList') IS NULL
BEGIN
   PRINT 'No active traces. Not exists tempdb..TraceQueueList table.'
   RETURN (0)
END

IF (@TraceID <> 0) 
   AND NOT EXISTS (SELECT 1 FROM tempdb..TraceQueueList WHERE TraceID = @TraceID)
BEGIN
   PRINT 'No active Trace : TraceID(' + CAST(@TraceID AS VARCHAR(5)) + ')'
   RETURN (0)
END

IF (@TraceID <> 0) 
   AND EXISTS (SELECT 1 FROM tempdb..TraceQueueList WHERE TraceID = @TraceID)
BEGIN 
  UPDATE tempdb..TraceQueueList 
  SET TraceStatus = 'Stopped'
  WHERE TraceID = @TraceID
END 

IF (@TraceID = 0)
BEGIN 
   UPDATE tempdb..TraceQueueList 
   SET TraceStatus = 'Stopped'
END

DECLARE cur_TraceStop CURSOR FAST_FORWARD
FOR SELECT TraceID
    FROM tempdb..TraceQueueList 
    WHERE TraceStatus = 'Stopped'    

OPEN cur_TraceStop

FETCH NEXT FROM cur_TraceStop
INTO @iTraceID

WHILE(@@fetch_status = 0)
BEGIN
  EXEC sp_trace_setstatus @iTraceId, 0 
  EXEC sp_trace_setstatus @iTraceId, 2 
  
  FETCH NEXT FROM cur_TraceStop
  INTO @iTraceID
END

CLOSE cur_TraceStop
DEALLOCATE cur_TraceStop

SELECT * FROM tempdb..TraceQueueList 
DELETE FROM tempdb..TraceQueueList 
WHERE TraceStatus = 'Stopped'

RETURN (0) -- sp_tracestop0 
GO 

GRANT EXECUTE ON sp_tracestop0 TO PUBLIC
GO


/************************************************************************************************
-- sp_umsstats0
-- ´ë±â ÀÚ¿ø Á¤º¸¸¦ ¼öÁýÇÏ°í sp_getwaitstats0¸¦ ÅëÇØ Ãâ·ÂÇÑ´Ù.
-- EXEC dbo.sp_umsstats0; EXEC sp_umsstats0 4, 10, 's';
************************************************************************************************/
USE master
GO

IF OBJECT_ID(N'dbo.sp_umsstats0') IS NOT NULL
    DROP PROC sp_umsstats0;
GO

CREATE PROC sp_umsstats0 
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- ÀÌ ³»¿ëÀº
-- http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/31577/
-- ¸¦ ÂüÁ¶ÇÏ¿´½À´Ï´Ù.

DECLARE @statistics varchar(32)
DECLARE @value FLOAT
DECLARE @Scheduler_ID INT
DECLARE @Num_users INT
DECLARE @Num_runnable INT
DECLARE @Num_workers INT
DECLARE @Idle_workers INT
DECLARE @Work_queued INT
DECLARE @cntxt_switches FLOAT
DECLARE @cntxt_switches_idle FLOAT

CREATE TABLE #umsstats
( [Statistics] varchar(32) NOT NULL
, [Value] FLOAT NOT NULL
)

CREATE TABLE #umsstats2
( [Scheduler_ID] INT NOT NULL
, [Num_Users] INT NOT NULL
, [Num_Runnable] INT NOT NULL
, [Num_Workers] INT NOT NULL
, [Idle_Workers] INT NOT NULL
, [Work_Queued] INT NOT NULL
, [Cntxt_Switches] FLOAT NOT NULL
, [Cntxt_Switches_idle] FLOAT NOT NULL
, [DateTime] DATETIME default GETDATE()
)

INSERT INTO #umsstats 
EXEC('DBCC SQLPERF(UMSSTATS) ')

DECLARE umsstats_cursor CURSOR 
FOR SELECT * FROM #umsstats

OPEN umsstats_cursor

FETCH next FROM umsstats_cursor 
INTO @statistics,@value

WHILE @@fetch_status = 0
BEGIN
  IF LTRIM(RTRIM(@statistics)) = 'Scheduler ID'  SET @scheduler_id = @value
  IF LTRIM(RTRIM(@statistics)) = 'num users'  SET @num_users = @value
  IF LTRIM(RTRIM(@statistics)) = 'num runnable'  SET @num_runnable = @value
  IF LTRIM(RTRIM(@statistics)) = 'num workers'  SET @num_workers = @value
  IF LTRIM(RTRIM(@statistics)) = 'idle workers'  SET @idle_workers = @value
  IF LTRIM(RTRIM(@statistics)) = 'work queued'  SET @work_queued = @value
  IF LTRIM(RTRIM(@statistics)) = 'cntxt switches' SET @cntxt_switches = @value
  IF LTRIM(RTRIM(@statistics)) = 'cntxt switches(idle)'
  BEGIN
    SET @cntxt_switches_idle = @value
    INSERT INTO #umsstats2( Scheduler_ID, Num_users, Num_runnable, Num_workers, Idle_workers
                          , Work_queued, cntxt_switches, cntxt_switches_idle)
    VALUES ( @Scheduler_ID, @Num_users, @Num_runnable, @Num_workers
           , @Idle_workers, @Work_queued, @cntxt_switches, @cntxt_switches_idle)
  END

  FETCH next FROM umsstats_cursor 
  INTO @statistics,@value
END

SELECT * FROM #umsstats2

CLOSE umsstats_cursor
DEALLOCATE umsstats_cursor

RETURN (0) -- sp_umsstats0
go

GRANT EXECUTE ON sp_umsstats0 TO PUBLIC
GO


/************************************************************************************************
-- sp_filestats0
-- ·Î±× ÆÄÀÏÀ» Æ÷ÇÔÇÏ¿© µ¥ÀÌÅͺ£À̽º ÆÄÀÏÀÇ I/O Åë°è Á¤º¸¸¦ º¸¿©ÁØ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('dbo.sp_filestats0') IS NOT NULL
    DROP PROC sp_filestats0
GO

CREATE PROC sp_filestats0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @filestats0 TABLE 
( dbid INT NOT NULL
, fileid INT NOT NULL
, timestamp BIGINT NOT NULL
, numberreads BIGINT NOT NULL
, numberwrites BIGINT NOT NULL
, bytesread BIGINT NOT NULL
, byteswritten BIGINT NOT NULL
, iostallms BIGINT NOT NULL
)

INSERT INTO @filestats0
SELECT * FROM ::fn_virtualfilestats(-1,-1)

CREATE TABLE #sysfiles0 
(dbname SYSNAME, filename SYSNAME, fileid INT)

EXEC sp_MSforeachdb 'USE [?]; INSERT INTO #sysfiles0 SELECT ''?'', name, fileid FROM dbo.sysfiles'

SELECT db_name(a.dbid) "DATABASE"
, b.filename "LOGICAL_FILE"
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,numberreads),112),'.00','') "NUMBER_READS"
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,bytesread),112),'.00','') "BYTES_READ"
, '(n/a)' "READS_WAIT_ms"
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,numberwrites),112),'.00','') "NUMBER_WRITES"
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,byteswritten),112),'.00','') "BYTES_WRITTEN"
, '(n/a)' "WRITES_WAIT_ms"
, REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,iostallms),112),'.00','') "SUMMARY_WAIT_ms"
FROM @filestats0 a
INNER JOIN #sysfiles0 b
  ON db_name(a.dbid) = b.dbname
  AND a.fileid = b.fileid

RETURN (0) -- sp_filestats0
GO

GRANT EXECUTE ON sp_filestats0 TO PUBLIC
GO


/************************************************************************************************
-- sp_cacheobject0
-- ij½Ã Á¤º¸ Ãâ·Â
-- ÆĶó¹ÌÅÍ´Â DB¸í ÀÔ·Â, ÀÔ·ÂÇÏÁö ¾ÊÀ¸¸é Àüü DB ij½Ã Á¤º¸ Ãâ·Â
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_cacheobject0') IS NOT NULL
   DROP PROCEDURE sp_cacheobject0
GO

CREATE PROC sp_cacheobject0    
  @db_nm SYSNAME = NULL  
AS
  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
  IF (@db_nm IS NULL)
  BEGIN
      SELECT db_name(dbid) "DATABASE", objtype "OBJECT_TYPE", cacheobjtype "CACHE_OBJECT_TYPE"
           , usecounts "USE_COUNT", refcounts "REF_COUNT", sqlbytes "SQL_bt", sql "SQL_STATEMENT"
      FROM master..syscacheobjects
      ORDER BY db_name(dbid)
  END
  ELSE
  BEGIN
      IF db_id(@db_nm) IS NULL
      BEGIN
          PRINT '!! Input parameter ''' + @db_nm  + ''' is not database name in this server.'
      END       
      ELSE
      BEGIN
          SELECT db_name(dbid) "DATABASE", objtype "OBJECT_TYPE", cacheobjtype "CACHE_OBJECT_TYPE"
               , usecounts "USE_COUNT", refcounts "REF_COUNT", sqlbytes "SQL_bt", sql "SQL_STATEMENT"
          FROM master..syscacheobjects
          WHERE db_name(dbid) = @db_nm
          ORDER BY db_name(dbid)
      END
  END
  RETURN (0)
GO

EXEC sp_MS_marksystemobject 'sp_cacheobject0'
GO

GRANT EXECUTE ON sp_cacheobject0 TO PUBLIC
GO


/************************************************************************************************
-- sp_login0
-- ·Î±×ÀÎ Á¤º¸ Ãâ·Â
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_login0') IS NOT NULL
   DROP PROCEDURE sp_login0
GO

CREATE PROC sp_login0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
SELECT name "LOGIN"
     , CASE WHEN isntgroup = 1 and isntuser = 0 THEN 'WINDOWS_GROUP'
            WHEN isntgroup = 0 and isntuser = 1 THEN 'WINDOWS_LOGIN'
            WHEN isntgroup = 0 and isntuser = 0 THEN 'SQL_LOGIN'
            ELSE '?'
       END "LOGIN_TYPE"
     , dbname "DEFAULT_DATABASE"
     , createdate "CREATE_DATE"
     , updatedate "MODIFY_DATE"
     , '(n/a)' "IS_DISABLED"
FROM master.dbo.syslogins

RETURN (0)
GO

EXEC sp_MS_marksystemobject 'sp_login0'
GO

GRANT EXECUTE ON sp_login0 TO PUBLIC
GO


/************************************************************************************************
-- sp_user0
-- ·Î±×ÀÎ Á¤º¸ Ãâ·Â
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_user0') IS NOT NULL
   DROP PROCEDURE sp_user0
GO

CREATE PROC sp_user0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT b.name "LOGIN"
     , a.name "USER"
     , '(n/a)' "DEFAULT_OWNER"
     , a.createdate "CREATE_DATE"
     , a.updatedate "MODIFY_DATE"
FROM dbo.sysusers a
LEFT OUTER JOIN master.dbo.syslogins b
ON a.sid = b.sid
WHERE a.issqluser = 1
ORDER BY b.name, a.name

RETURN (0)
GO

EXEC sp_MS_marksystemobject 'sp_user0'
GO

GRANT EXECUTE ON sp_user0 TO PUBLIC
GO