티스토리 뷰

데이터베이스/MSSQL

테이블 정보

메모하는습관 2009.06.09 13:44

테이블 정보

SELECT table_catalog, table_name, table_schema, table_type, ISNULL(value, '')
FROM INFORMATION_SCHEMA.TABLES t1 LEFT JOIN
	(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 스키마명, 'table', 테이블명, DEFAULT, DEFAULT)) t2
	ON t1.table_name = t2.objname Collate KOREAN_WANSUNG_CI_AS
WHERE table_catalog = 데이터베이스명
	AND table_schema = 스키마명
	AND table_name = 테이블명



테이블내 컬럼 정보

쿼리1)
SELECT t2.name AS 'schema_name', t1.name AS 'table_name', 
		t3.name AS 'column_name', t4.name AS 'column_type',
		CASE t4.name
			WHEN  'char' THEN t3.max_length
			WHEN  'varchar' THEN t3.max_length
			WHEN  'nchar' THEN t3.max_length/2
			WHEN  'nvarchar' THEN t3.max_length/2
			ELSE ''
		END AS column_length,
	CASE WHEN t3.is_nullable = 1 THEN 'null' ELSE 'not null' END AS nullable,
	ISNULL(t5.definition, '') AS column_default,
	ISNULL(t6.value, '') AS column_description
FROM sys.tables t1 join sys.schemas t2
	ON t1.schema_id = t2.schema_id JOIN sys.columns t3
	ON t1.object_id = t3.object_id JOIN sys.types t4
	ON t3.user_type_id = t4.user_type_id LEFT OUTER JOIN sys.default_constraints t5
	ON t1.object_id = t5.parent_object_id 
	AND t3.column_id = t5.parent_column_id LEFT OUTER JOIN 
	(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'astro_pl', 'table', 테이블명, 'column', DEFAULT)) t6
	ON t3.name = t6.objname Collate KOREAN_WANSUNG_CI_AS 
WHERE t2.name = 스키마명
	AND t1.name = 테이블명


쿼리2)
SELECT table_schema, table_name, column_name,
	data_type,
	CASE data_type
		WHEN  'char' then CAST(character_octet_length as varchar)
		WHEN  'varchar' then CAST(character_octet_length as varchar)
		WHEN  'nchar' then CAST(character_octet_length / 2 as varchar)
		WHEN  'nvarchar' then CAST(character_octet_length / 2 as varchar)
		ELSE ''
	END AS column_length,
	CASE WHEN is_nullable = 'yes' THEN 'null' ELSE 'not null' END AS nullable,
	column_default,
	ISNULL(t2.value, '') AS 'description'
FROM information_schema.[columns] t1 LEFT OUTER JOIN
	(SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 스키마명, 'table', 테이블명, 'column', DEFAULT)) t2
	ON t1.column_name = t2.objname COLLATE KOREAN_WANSUNG_CI_AS
WHERE table_name = 테이블명
ORDER BY t1.ordinal_position);



인덱스 목록 정보
SELECT t2.name, t2.is_unique,
	CASE t2.is_primary_key WHEN 1 THEN 'PK'
		ELSE 
			CASE t2.is_unique_constraint WHEN 1 THEN 'UK'
				ELSE 'IX' END
			END AS idx_type,
	t2.type_desc
FROM sys.tables t1 JOIN sys.indexes t2
	ON t1.object_id = t2.object_id
WHERE t1.name = 'tb_bill_cash'
	AND t2.name IS NOT NULL
ORDER BY t2.index_id



인덱스 필드 정보

SELECT t2.name, t2.name,
	t3.index_column_id,
	CASE t3.is_descending_key
		WHEN 0 THEN 'ASC'
		ELSE 'DESC'
	END AS descend, t4.name
FROM sys.tables t1 JOIN sys.indexes t2
	ON t1.object_id = t2.object_id JOIN sys.index_columns t3
	ON t1.object_id = t3.object_id
	AND t2.index_id = t3.index_id JOIN sys.columns t4
	ON t1.object_id = t4.object_id
	AND t3.column_id = t4.column_id
WHERE t1.name = 'tb_lounge'
	AND
ORDER BY t3.index_column_id

'데이터베이스 > MSSQL' 카테고리의 다른 글

LOCK 처리 방법  (0) 2009.07.15
Connection Count  (0) 2009.06.10
테이블 정보  (0) 2009.06.09
함수  (0) 2009.06.05
변수  (0) 2009.06.05
DB Server Link  (0) 2009.06.03
댓글
댓글쓰기 폼