--테이블

select

             a.name schema_name

,            isnull(

                           case

                                        when c.column_id = 1 then b.name

                                        when c.column_id = 2 then d.table_name

                           else '' end, '') table_name

,            c.name column_name

,            isnull(d.column_name, '') column_desc1

,            isnull('(PK, ' + convert(varchar, e.type_desc) + ') ', '') + isnull('(FK,' + object_name(f.referenced_object_id), '') column_desc2

,            g.name date_type

,            case when g.name IN ('nchar', 'nvarchar') AND c.max_length <> -1 then c.max_length/2 else c.max_length end column_length

,            c.precision

,            c.scale

,            case when c.is_nullable = 1 then 'NULL' else 'NOT NULL' end is_nullable

,            case

                           when c.is_identity = 1 then

                                                                                                                       (select '(' + convert(varchar, seed_value)  + ',' + convert(varchar, increment_value) + ')'

                                                                                                                       from sys.identity_columns where b.object_id = object_id)

                           else ''

             end is_identity

--,         isnull(h.name, '') default_constraints

,            isnull(h.definition, '') default_value

from sys.schemas a inner join sys.objects b

on a.schema_id = b.schema_id inner join sys.all_columns c

on b.object_id = c.object_id

and b.type = 'U' left join (

                                                                                             select

                                                                                                          a.major_id object_id

                                                                                             ,            b.minor_id column_id

                                                                                             ,            a.value table_name

                                                                                             ,            b.value column_name

                                                                                             from sys.extended_properties a inner join sys.extended_properties b

                                                                                             on a.major_id = b.major_id

                                                                                             and b.minor_id > 0

                                                                                             and a.minor_id = 0) d

on c.object_id = d.object_id

and c.column_id = d.column_id left join (

                                                                                                                                     select

                                                                                                                                                  a.object_id

                                                                                                                                     ,            a.column_id

                                                                                                                                     ,            b.type_desc

                                                                                                                                     from sys.index_columns a inner join sys.indexes b

                                                                                                                                     on a.object_id = b.object_id

                                                                                                                                     and a.index_id = b.index_id

                                                                                                                                     and b.is_primary_key = 1

                                                                                                                                     ) e

on b.object_id = e.object_id

and c.column_id = e.column_id left join sys.foreign_key_columns f

on b.object_id = f.parent_object_id

and c.column_id = f.parent_column_id inner join sys.types g

on c.system_type_id = g.system_type_id

and c.user_type_id = g.user_type_id left join sys.default_constraints h

on c.default_object_id = h.object_id

where b.name <> 'sysdiagrams'

order by b.name , b.object_id, c.column_id

--인덱스

select

             c.name table_name

,            b.name index_name

,            b.type_desc index_type

,            b.is_primary_key

,            b.is_unique

,            b.fill_factor

,            b.allow_row_locks

,            b.allow_page_locks

from sys.index_columns a inner join sys.indexes b

on a.object_id = b.object_id

and a.index_id = b.index_id inner join sys.objects c

on a.object_id = c.object_id

and c.type = 'U'

where c.name <> 'sysdiagrams'