SQL Server 테이블 정보 조회(sp_help / sp_column)

컬럼정도, 통계 , select 컬럼정보, 테이블별 사이즈 확인 가능합니다.

 

sp_help 에 비해 축약된 필요한 정보만 노출합니다. 

 

컬럼 정보를 제외하고 다른 항목은 text 창에 print 합니다.

ALTER PROCEDURE [dbo].[sp_Table]    
(    
   @TableName varchar(50)
)    
AS    

SET NOCOUNT ON    
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED     

SET @TableName = replace(replace(@TableName, char(13), ''), char(10), '');
SET @TableName = replace(@TableName, char(9), '')
SET @TableName = RTRIM(LTRIM(@TableName))

DECLARE @DBNAME nVARCHAR (30) = DB_NAME() -- PARSENAME(@TableName,3)
DECLARE @SCHEMA nVARCHAR (5) = ISNULL(PARSENAME(@TableName, 2),'DBO')
DECLARE @TABLE nVARCHAR (50) = PARSENAME(@TableName, 1)

DECLARE @Description VARCHAR(100)

-- 테이블 정보
DECLARE @ObjectID VARCHAR(20) = object_id (@DBNAME + '.'+@SCHEMA+'.' + @TABLE)

IF @ObjectID IS NULL
BEGIN
	DECLARE @QRY NVARCHAR (max)
	SELECT @QRY = ISNULL(@QRY + 'UNION ALL','') + ' SELECT ''' + name +''' AS DB ,NAME FROM ' + name +'.SYS.sysobjects WHERE name= '''+@TABLE+''''+CHAR(13) 
		FROM SYS.sysdatabases 
	EXEC(@QRY)
END

IF @OBJECTID IS NULL
BEGIN
       SELECT ' DB에 테이블이존재하지않습니다 '
       RETURN
END

SELECT @Description = cast(p.value as varchar(100)) FROM sys.tables AS tbl
INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
WHERE
(p.name=N'MS_Description')and((tbl.object_id = @ObjectID and SCHEMA_NAME(tbl.schema_id)=N'dbo'))

IF OBJECT_ID('tempdb.dbo.#TABLE_INFO') IS NULL 
BEGIN
	-- 컬럼 정보
	CREATE TABLE #TABLE_INFO(
		[DBName] [varchar](30) NOT NULL,
		[Table_Name] [sysname] NOT NULL,
		[Table_Description] [varchar](1) NOT NULL,
		[Column_Name] [sysname] NULL,
		[PK] [varchar](1) NOT NULL,
		[Data_Type] [sysname] NOT NULL,
		[Length] [int] NOT NULL,
		[Column_Default] [nvarchar](30) NULL,
		[Is_Nullable] [varchar](1) NOT NULL,
		[Description] [varchar](8000) NULL
	)
END
ELSE
BEGIN 
	TRUNCATE TABLE #TABLE_INFO
END

INSERT INTO #TABLE_INFO
SELECT 
	@DBNAME AS DBName,
	Table_Name = A.name,
	Table_Description = '',
	Column_Name = B.name, 
	PK = ISNULL(CAST(TC.ORDINAL_POSITION AS VARCHAR(2)),''),
	Data_Type = T.name,
	Length = ISNULL(SC.character_maximum_length,B.MAX_LENGTH),
	Column_Default = CAST(ISNULL(G.Definition, H.Definition) AS VARCHAR),
	Is_Nullable = '',
	Description = REPLACE(REPLACE(CAST(C.Value AS VARCHAR(8000)),CHAR(13),''),CHAR(10),'')
FROM SYS.OBJECTS A
INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID = B.OBJECT_ID 
INNER JOIN SYS.TYPES T ON B.USER_TYPE_ID = T.USER_TYPE_ID
INNER JOIN INFORMATION_SCHEMA.COLUMNS SC ON SC.COLUMN_NAME = B.NAME AND SC.TABLE_NAME = A.NAME
LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES C ON A.OBJECT_ID = C.MAJOR_ID AND B.COLUMN_ID = C.MINOR_ID  AND C.name = 'MS_Description' AND C.class_desc = 'OBJECT_OR_COLUMN'
LEFT OUTER JOIN SYS.DEFAULT_CONSTRAINTS G ON B.DEFAULT_OBJECT_ID = G.OBJECT_ID AND B.COLUMN_ID = G.PARENT_COLUMN_ID
LEFT OUTER JOIN SYS.SQL_MODULES H ON B.DEFAULT_OBJECT_ID = H.OBJECT_ID
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS TC ON TC.TABLE_NAME = A.name AND TC.COLUMN_NAME = SC.COLUMN_NAME
WHERE B.object_id = @ObjectID
ORDER BY  A.name, B.COLUMN_ID

SELECT DBName, Table_Name, @Description as [Description], Column_Name, PK, Data_Type, Length, Column_Default, Is_Nullable, Description FROM #TABLE_INFO

DECLARE @COLUMN_QRY VARCHAR(MAX)

SELECT @COLUMN_QRY =   ISNULL(@COLUMN_QRY + ', ' ,'') + Column_Name   FROM #TABLE_INFO


PRINT  CHAR(13)
PRINT '--------------------------------------' 
PRINT CAST(@COLUMN_QRY AS NTEXT)
PRINT '--------------------------------------' 
PRINT  CHAR(13)

/*******************************************************************************/
/*통계 정보*/
/*******************************************************************************/

DECLARE @STATISTICS_INFO VARCHAR(MAX) = ''
DECLARE @DBCC_INFO VARCHAR(MAX) = ''

SELECT 
	@STATISTICS_INFO = @STATISTICS_INFO + CHAR(13)   +' '+ CAST(s.NAME AS CHAR(40)) + CAST(b.NAME AS CHAR(70)), 
	@DBCC_INFO += CHAR(13) +'DBCC SHOW_STATISTICS('+OBJECT_NAME(@ObjectID)+', ' +s.NAME+')' 
from ACCT_LO.sys.stats AS s  
OUTER APPLY
(
	SELECT STUFF((
		select  ISNULL(', ' + c.name,'')
		from ACCT_LO.sys.stats_columns AS sc   
		INNER JOIN ACCT_LO.sys.columns AS c   
		ON sc.object_id = c.object_id AND c.column_id = sc.column_id		
		where s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
	FOR XML PATH('')),1,1,'') AS NAME
) AS B WHERE s.object_id = @ObjectID; 


PRINT  CHAR(13)
PRINT  '-- 통계정보'
PRINT '----------------------------------------------------------------------------' 
PRINT CAST(@STATISTICS_INFO AS NTEXT)
PRINT '----------------------------------------------------------------------------' 
PRINT CAST(@DBCC_INFO AS NTEXT)
PRINT '----------------------------------------------------------------------------' 
PRINT  CHAR(13)


/*******************************************************************************/
/*이하 용량 정보*/
/*******************************************************************************/
DECLARE @TABLE_INFO VARCHAR(MAX) = ''

SELECT @TABLE_INFO += convert(varchar (200), min( o.name )) + '     ',
       @TABLE_INFO += CAST(convert( int, ltrim( str ( sum( cast(reserved as bigint)) * 8192 / 1024., 15, 0 ))) AS VARCHAR) + ' KB              ',
       @TABLE_INFO += CAST(convert( int, ltrim( str ( sum( cast(reserved as bigint)) * 8192 / 1024. / 1024., 15, 0 ))) AS VARCHAR) + ' MB           ',
       @TABLE_INFO += CAST(convert( int, ltrim( str ( sum( cast(reserved as bigint)) * 8192 / 1024. / 1024. / 1024., 15 , 0 ))) AS VARCHAR) + ' GB   ' 
FROM sys.sysindexes i
  INNER JOIN sys.sysobjects o ON ( o.id = i.id )
WHERE i .indid IN ( 0, 1 , 255) AND  o . xtype = 'U' and o.id = @ObjectID
GROUP BY i. id


PRINT  CHAR(13)
PRINT '------------------------------------------------------------------------------------------------------------------'
PRINT @TABLE_INFO
PRINT '------------------------------------------------------------------------------------------------------------------'


SET NOCOUNT OFF