Table definition 테이블 정의서 인덱스 정의서

테이블 정의서 2008 @@

select
case row_number() over(partition by o.name order by o.name, c.column_id)
when 1 then o.name else ” end “Table Name”
,c.name “Column Name”
,case t.name
when ‘varchar’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘char’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
when ‘datetime’ then t.name
else t.name+'(‘+cast(c.precision as varchar)+’,’+cast(c.scale as varchar)+’)’ end
as “Column Type”
,case c.is_nullable when 0 then ‘NOT NULL’ else ” end “Nullable”
,isnull(d.definition,”) “Column Default”
,case when k.index_id is null then ” else ‘YES’ end “PK”
,e.value “Column Comment”
from sys.tables o
join sys.columns c on(o.object_id = c.object_id and o.type=’U’)
join sys.types t on(c.system_type_id = t.system_type_id and t.schema_id=4)
left outer join sys.extended_properties e on(c.object_id = e.major_id and c.column_id = e.minor_id)
left outer join sys.index_columns k on(c.object_id = k.object_id and c.column_id = k.column_id and index_id=1)
left outer join sys.default_constraints d on(c.object_id=d.parent_object_id and c.column_id=d.parent_column_id and d.type=’D’)
where o.name <> ‘sysdiagrams’
order by o.name, c.column_id
;

[출처] [2008] 테이블 칼럼 정의서 뽑는 쿼리|작성자 젠틀맨

인덱스 정의서 2008

SELECT
CASE table_RN WHEN 1 THEN A.table_name ELSE ” END table_name,
CASE RN WHEN 1 THEN A.index_name ELSE ” END index_name,
index_column_id,
colum_name,
is_unique,
is_primary_key
FROM
(

select
ROW_NUMBER() OVER(ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) sort_RN,
ROW_NUMBER() OVER(PARTITION BY o.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) table_RN,
ROW_NUMBER() OVER(PARTITION BY o.name, i.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) RN,
o.name table_name,
i.name index_name,
b.name colum_name,
a.index_column_id,
case i.is_unique when 1 then ‘YES’ else ” end is_unique,
case i.is_primary_key when 1 then ‘YES’ else ” end is_primary_key,
i.is_primary_key is_primary_key_idx
from
sys.all_objects o join
sys.indexes i on(o.object_id=i.object_id) join
sys.index_columns a on(i.object_id=a.object_id and i.index_id = a.index_id)
join sys.columns b on
(a.object_id = b.object_id and a.column_id = b.column_id)
where o.type=’U’
and i.index_id > 0
and o.name != ‘sysdiagrams’
) A
order by sort_RN