티스토리 뷰
테이블 정보
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.05 |
변수 (0) | 2009.06.05 |
DB Server Link (0) | 2009.06.03 |
- Total
- Today
- Yesterday