\ud14c\uc774\ube14\u00a0\uc815\uc758\uc11c\u00a02008 @@<\/p>\n
select
\ncase row_number() over(partition by o.name order by\u00a0 o.name, c.column_id)
\nwhen 1 then o.name else ” end “Table Name”
\n,c.name “Column Name”
\n,case t.name
\nwhen ‘varchar’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
\nwhen ‘char’ then t.name +'(‘+cast(c.max_length as varchar) +’)’
\nwhen ‘datetime’ then t.name
\nelse t.name+'(‘+cast(c.precision as varchar)+’,’+cast(c.scale as varchar)+’)’ end
\nas “Column Type”
\n,case c.is_nullable when 0 then ‘NOT NULL’ else ” end “Nullable”
\n,isnull(d.definition,”) “Column Default”
\n,case when k.index_id is null then ” else ‘YES’ end “PK”
\n,e.value “Column Comment”
\nfrom sys.tables o
\njoin sys.columns c on(o.object_id = c.object_id and o.type=’U’)
\njoin sys.types t on(c.system_type_id = t.system_type_id and t.schema_id=4)
\nleft outer join sys.extended_properties e on(c.object_id = e.major_id and c.column_id = e.minor_id)
\nleft outer join sys.index_columns k on(c.object_id = k.object_id and c.column_id = k.column_id and index_id=1)
\nleft 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’)
\nwhere o.name <> ‘sysdiagrams’
\norder by\u00a0 o.name, c.column_id
\n;<\/p>\n
[\ucd9c\ucc98]<\/strong>\u00a0[2008] \ud14c\uc774\ube14 \uce7c\ub7fc \uc815\uc758\uc11c \ubf51\ub294 \ucffc\ub9ac<\/a>|\uc791\uc131\uc790<\/strong>\u00a0\uc820\ud2c0\ub9e8<\/a><\/p>\n<\/div>\n \uc778\ub371\uc2a4\u00a0\uc815\uc758\uc11c\u00a02008<\/p>\n SELECT select [\ucd9c\ucc98]<\/strong>\u00a0[2008] \uc778\ub371\uc2a4\uc815\ubcf4 \ubf51\ub294 \ucffc\ub9ac<\/a>|\uc791\uc131\uc790<\/strong>\u00a0\uc820\ud2c0\ub9e8<\/a><\/p>\n<\/div>\n exerd \ubaa8\ub378\ub9c1<\/p>\n http:\/\/blog.naver.com\/PostView.nhn?blogId=rmflt&logNo=110181374443<\/a><\/p>\n \ub370\uc774\ud0c0 \ubaa8\ub378\ub9c1\uc758 \uc774\ud574<\/p>\n http:\/\/www.dbguide.net\/db.db?cmd=view&boardUid=148182&boardConfigUid=9&categoryUid=216&boardIdx=132&boardStep=1<\/a><\/p>\n Download sqljdbc4-2.0.jar<\/b><\/p>\n
\nCASE table_RN WHEN 1 THEN A.table_name ELSE ” END table_name,
\nCASE RN WHEN 1 THEN A.index_name ELSE ” END index_name,
\nindex_column_id,
\ncolum_name,
\nis_unique,
\nis_primary_key
\nFROM
\n(<\/p>\n
\nROW_NUMBER() OVER(ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) sort_RN,
\nROW_NUMBER() OVER(PARTITION BY o.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) table_RN,
\nROW_NUMBER() OVER(PARTITION BY o.name, i.name ORDER BY o.name, i.is_primary_key desc, i.name, a.index_column_id) RN,
\no.name table_name,
\ni.name index_name,
\nb.name colum_name,
\na.index_column_id,
\ncase i.is_unique when 1 then ‘YES’ else ” end is_unique,
\ncase i.is_primary_key when 1 then ‘YES’ else ” end is_primary_key,
\ni.is_primary_key is_primary_key_idx
\nfrom
\nsys.all_objects o join
\nsys.indexes i on(o.object_id=i.object_id) join
\nsys.index_columns a on(i.object_id=a.object_id and i.index_id = a.index_id)
\njoin sys.columns b on
\n(a.object_id = b.object_id and a.column_id = b.column_id)
\nwhere o.type=’U’
\nand i.index_id > 0
\nand o.name != ‘sysdiagrams’
\n)\u00a0A
\norder by sort_RN<\/p>\n