| <BASE class="MIME" href="http://www.devpia.com/Namo/ActiveSquare6/kor/pages/defdoc.htm">
MSSQL DB테이블 명세서를 만들기 귀찮아하던 어떤분의 직장후배님께서 반나절만에 만드신 소스입니다.
디비연결부분만 수정하셔서 바로 실행하시면 됩니다.
<% option explicit Response.Expires = -1000 Dim SQL, i,tmpLen, j, k , MaxK, aryRef, M, aryFK, MaxFK, ProjectName, YourName,tmpTABLEName, aryPk Dim TABLE_des, aryTblist, aryIndex, s, u, tmpIndexkeys , l,forExcel
forExcel = Request.QueryString("forExcel") if forExcel = "Y" then response.contenttype="application/vnd.ms-excel" Response.AddHeader "Content-Disposition","attachment;filename=TABLE_LIST.xls" end if
ProjectName = "TABLE INFOMATION" YourName = "TRICYCLE DEV. TEAM" '#테이블 목록 조회 SQL = "select so.name, sp.value from " &_ " ( " &_ " select id, name from sysobjects " &_ " where xtype='U' " &_ " and name <> 'dtproperties' " &_ " ) so " &_ " left outer join " &_ " ( " &_ " select id, value from sysproperties " &_ " where type=3 " &_ " and name like 'MS%' " &_ " ) sp on " &_ "so.id = sp.id " &_ "order by so.name asc " '---------------------------------------------------------------------------------------------------------------------- ' // 디비 연결 '---------------------------------------------------------------------------------------------------------------------- Dim adoConn, adoRS, aryTABLEInfo SET adoConn = Server.CreateObject("ADODB.Connection") SET adoRS = Server.CreateObject("ADODB.RecordSet")
adoConn.open "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=1234;User ID=sa;Password=1234;Persist Security Info=True" adoRS.open SQL, adoConn, 0 if not adoRS.Eof then aryTbList = adoRS.GetRows() else '# 목록이 없을 경우 스크립트 중단 Response.Write "TABLE이 존재하지 않습니다." SET adoRS = Nothing adoConn.close SET adoConn = nothing Response.End end if adoRS.close
'# 테이블 정보조회 SQL = "SELECT " & chr(13) &_ " X1.TABLE_QUALIFIER,X1.TABLE_OWNER, X1.TABLE_NAME, X1.COLUMN_NAME " & chr(13) &_ " ,X1.TYPE_NAME, X1.LENGTH, X1.IS_NULLABLE,X1.value ,X1.COLUMN_DEF " & chr(13) &_ " ,IS_PK = case " & chr(13) &_ " when X3.colid is null then null " & chr(13) &_ " else 'PK' " & chr(13) &_ " end " & chr(13) &_ " ,IS_FK = case " & chr(13) &_ " when X2.colnm is null then null " & chr(13) &_ " else 'FK' " & chr(13) &_ " end " & chr(13) &_ "FROM " & chr(13) &_ " ( " & chr(13) &_ " SELECT T2.GID, T2.TABLE_QUALIFIER,T2.TABLE_OWNER, T2.TABLE_NAME, T2.COLUMN_NAME " & chr(13) &_ " , T2.TYPE_NAME, T2.LENGTH, T2.IS_NULLABLE,T1.value ,T2.COLUMN_DEF, T2.colid " & chr(13) &_ " FROM " & chr(13) &_ " ( " & chr(13) &_ " SELECT " & chr(13) &_ " GID = O.ID " & chr(13) &_ " ,TABLE_QUALIFIER = convert(sysname,DB_NAME()) " & chr(13) &_ " ,TABLE_OWNER = convert(sysname,USER_NAME(o.uid)) " & chr(13) &_ " ,TABLE_NAME = convert(sysname,o.name) " & chr(13) &_ " ,COLUMN_NAME = convert(sysname,c.name) " & chr(13) &_ " ,TYPE_NAME = convert (sysname,case " & chr(13) &_ " when t.xusertype > 255 then t.name " & chr(13) &_ " else d.TYPE_NAME collate database_default " & chr(13) &_ " end) " & chr(13) &_ " ,LENGTH = convert(int,case " & chr(13) &_ " when type_name(d.ss_dtype) IN ('numeric','decimal') then " & chr(13) &_ " OdbcPrec(c.xtype,c.length,c.xprec)+2 " & chr(13) &_ " else " & chr(13) &_ " isnull(d.length, c.length) " & chr(13) &_ " end) " & chr(13) &_ " ,COLUMN_DEF = text " & chr(13) &_ " ,IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))) " & chr(13) &_ " ,c.colid " & chr(13) &_ " FROM " & chr(13) &_ " sysobjects o, " & chr(13) &_ " master.dbo.spt_datatype_info d, " & chr(13) &_ " systypes t, " & chr(13) &_ " syscolumns c " & chr(13) &_ " LEFT OUTER JOIN syscomments m on " & chr(13) &_ " c.cdefault = m.id " & chr(13) &_ " AND m.colid = 1 " & chr(13) &_ " WHERE " & chr(13) &_ " o.id = c.id " & chr(13) &_ " AND o.xtype = 'U' " & chr(13) &_ " AND t.xtype = d.ss_dtype " & chr(13) &_ " AND c.length = isnull(d.fixlen, c.length) " & chr(13) &_ " AND (d.ODBCVer is null or d.ODBCVer = 2) " & chr(13) &_ " AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) " & chr(13) &_ " AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) " & chr(13) &_ " AND c.xusertype = t.xusertype AND O.NAME<>'dtproperties' " & chr(13) &_ " ) T2 " & chr(13) &_ " LEFT OUTER JOIN ( " & chr(13) &_ " select * from sysproperties " & chr(13) &_ " where name = 'MS_Description' and smallid != 0 " & chr(13) &_ " ) T1 " & chr(13) &_ " on " & chr(13) &_ " T2.gid = T1.id " & chr(13) &_ " and T2.colid = T1.smallid " & chr(13) &_ " ) X1 " & chr(13) &_ " Left Outer join " & chr(13) &_ " ( " & chr(13) &_ " select parent_obj [tb_id] , sc.name[colnm] , sc.colid from sysobjects so " & chr(13) &_ " inner join sysreferences sf on " & chr(13) &_ " so.id = sf.constid " & chr(13) &_ " inner join syscolumns sc on " & chr(13) &_ " ( " & chr(13) &_ " sf.fkeyid = sc.id " & chr(13) &_ " and ( " & chr(13) &_ " sf.fkey1 = sc.colid or sf.fkey2 = sc.colid " & chr(13) &_ " or sf.fkey3 = sc.colid or sf.fkey4 = sc.colid " & chr(13) &_ " or sf.fkey5 = sc.colid or sf.fkey6 = sc.colid " & chr(13) &_ " or sf.fkey7 = sc.colid or sf.fkey8 = sc.colid " & chr(13) &_ " or sf.fkey9 = sc.colid or sf.fkey10 = sc.colid " & chr(13) &_ " or sf.fkey11 = sc.colid or sf.fkey12 = sc.colid " & chr(13) &_ " or sf.fkey13 = sc.colid or sf.fkey14 = sc.colid " & chr(13) &_ " or sf.fkey15 = sc.colid or sf.fkey16 = sc.colid " & chr(13) &_ " ) " & chr(13) &_ " ) " & chr(13) &_ " where so.xtype='F' " & chr(13) &_ " ) X2 on " & chr(13) &_ " X1.GID = X2.tb_id " & chr(13) &_ " and X1.colid = X2.colid " & chr(13) &_ " Left outer join ( " & chr(13) &_ " select o.id, c.colid from sysobjects o, syscolumns c, sysindexes i, sysindexkeys k " & chr(13) &_ " where " & chr(13) &_ " o.id = c.id " & chr(13) &_ " and i.id = k.id " & chr(13) &_ " and i.indid = k.indid " & chr(13) &_ " and k.id = c.id " & chr(13) &_ " and k.colid = c.colid " & chr(13) &_ " and i.name like 'PK%' " & chr(13) &_ " ) X3 on " & chr(13) &_ " X1.GID = X3.id " & chr(13) &_ " and X1.colid = X3.colid " & chr(13) &_ "order by X1.TABLE_QUALIFIER,X1.TABLE_OWNER, X1.TABLE_NAME,X1.colid "
'response.write replace(SQL,chr(13) , "<br>") adoRS.open SQL, adoConn, 0 if not adoRS.Eof then aryTABLEInfo = adoRS.GetRows() end if adoRS.close %>
<STYLE TYPE="text/css"> <!-- BODY { FONT-SIZE: 9pt }
TABLE { FONT-SIZE: 9pt } TD { FONT-FAMILY: 굴림체; FONT-SIZE: 9pt } INPUT { FONT: 9pt Verdana, Arial, san-serif; COLOR: #766d5b; BACKGROUND-COLOR: #ffffff ;scrollbar-face-color: #ffffff; --> </STYLE> <TABLE width='600' border="1" cellspacing="0" bordercolordark="white" bordercolorlight="#959595"> <TR height="21"> <TD colspan='7' bgcolor='#C9C9C9' align='center'>테이블 목록</TD> </TR> <TR height="21"> <TD colspan="2" bgcolor='#DFDFDF' align='center'>시스템</TD> <TD colspan='5'> <%=ProjectName%></TD> </TR> <TR height="21"> <TD colspan="2" bgcolor='#DFDFDF' align='center'>작성자</TD> <TD> <%=YourName%></TD> <TD bgcolor='#DFDFDF' align='center'>작성일자</TD> <TD colspan="3"> <%=Date()%></TD> </TR> <TR bgcolor='#DFDFDF' align='center'> <TD>No</TD> <TD>Table ID</TD> <TD>Table Name</TD> <TD colspan='4'>Description</TD> </TR> <% for i = 0 to Ubound(aryTbList,2) %> <TR> <TD align='center'><%=i+1%></TD> <TD> <a class='MIME' href="#<%=aryTbList(0,i)%>"><%=aryTbList(0,i)%></a></TD> <TD> <%=aryTbList(1,i)%></TD> <TD colspan='4'> </TD> </TR> <% next if i < 38 then for l=i+1 to 38 response.write "<tr height='21'><td align='center' >"&l&"</td><td> </td><td> </td><td colspan='4'> </td></tr>" next end if %> </TABLE> <TABLE width='600' border="0"> <TR> <TD height="20" colspan="7" align="center"><br> <input type="button" name="" value="EXCEL 로 변환" onclick="window.location='table.asp?forExcel=Y';"> <input type="button" name="" value="PRINT 하기" onclick="window.print();"><br><br> </TD> </TR> <TR> <TD height="20" colspan="7"> </TD> </TR> </TABLE> <% for i = 0 to Ubound(aryTABLEInfo,2) tmpTABLEName = aryTABLEInfo(2,i) '# 테이블 코멘트 SQL = " select sp.value from sysobjects so, sysproperties sp " &_ " where so.id = sp.id " &_ " and so.name = '"&tmpTABLEName&"' " &_ " and so.xtype = 'U' " &_ " and sp.type =3 " &_ " and sp.name like 'MS%' " adoRS.open SQL, adoConn, 0 if not adoRS.eof then TABLE_des = adoRS(0) else TABLE_des = null end if adoRS.close '# PK Select SQL = "select COLUMN_NAME " &_ " from information_schema.key_column_usage " &_ " where TABLE_name = '"&tmpTABLEName&"' " &_ " and constraint_name like 'PK%'" adoRS.open SQL, adoConn, 0 if not adoRS.eof then aryPK = adoRS.GetRows() else aryPK = null end if adoRS.close '# Reference Select SQL = "select object_name(rkeyid) from sysReferences " &_ "where object_name(fkeyid) = '"&tmpTABLEName&"'" adoRS.open SQL, adoConn, 0 if not adoRS.eof then aryRef = adoRS(0) else aryRef = null end if adoRS.close '# FK Select if not isNull(aryRef) then SQL="select COLUMN_NAME " &_ "from information_schema.key_column_usage " &_ "where TABLE_name = '"&tmpTABLEName&"' " &_ "and constraint_name like 'FK%'" adoRS.open SQL, adoConn, 0 if not adoRS.eof then aryFK = adoRS.GetRows() else aryFK = null end if adoRS.close end if
'# Index infomation SQL = "declare @empty varchar(1) " & chr(13) &_ "select @empty = '' " & chr(13) &_ "declare @des1 varchar(35), " & chr(13) &_ " @des2 varchar(35), " & chr(13) &_ " @des4 varchar(35), " & chr(13) &_ " @des32 varchar(35), " & chr(13) &_ " @des64 varchar(35), " & chr(13) &_ " @des2048 varchar(35), " & chr(13) &_ " @des4096 varchar(35), " & chr(13) &_ " @des8388608 varchar(35), " & chr(13) &_ " @des16777216 varchar(35) " & chr(13) &_ "select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1 " & chr(13) &_ "select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2 " & chr(13) &_ "select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4 " & chr(13) &_ "select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32 " & chr(13) &_ "select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64 " & chr(13) &_ "select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048 " & chr(13) &_ "select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096 " & chr(13) &_ "select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608 " & chr(13) &_ "select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216 " & chr(13) &_ "select o.name, " & chr(13) &_ " i.name, " & chr(13) &_ " 'index description' = convert(varchar(210), " & chr(13) &_ " case when (i.status & 16)<>0 then 'clustered' else 'nonclustered' end " & chr(13) &_ " + case when (i.status & 1)<>0 then ', '+@des1 else @empty end " & chr(13) &_ " + case when (i.status & 2)<>0 then ', '+@des2 else @empty end " & chr(13) &_ " + case when (i.status & 4)<>0 then ', '+@des4 else @empty end " & chr(13) &_ " + case when (i.status & 64)<>0 then ', '+@des64 else " & chr(13) &_ " case when (i.status & 32)<>0 then ', '+@des32 else @empty end end " & chr(13) &_ " + case when (i.status & 2048)<>0 then ', '+@des2048 else @empty end " & chr(13) &_ " + case when (i.status & 4096)<>0 then ', '+@des4096 else @empty end " & chr(13) &_ " + case when (i.status & 8388608)<>0 then ', '+@des8388608 else @empty end " & chr(13) &_ " + case when (i.status & 16777216)<>0 then ', '+@des16777216 else @empty end), " & chr(13) &_ " 'index column 1' = index_col(o.name,indid, 1), " & chr(13) &_ " 'index column 2' = index_col(o.name,indid, 2), " & chr(13) &_ " 'index column 3' = index_col(o.name,indid, 3), " & chr(13) &_ " 'index column 4' = index_col(o.name,indid, 4), " & chr(13) &_ " 'index column 5' = index_col(o.name,indid, 5), " & chr(13) &_ " 'index column 6' = index_col(o.name,indid, 6), " & chr(13) &_ " 'index column 7' = index_col(o.name,indid, 7), " & chr(13) &_ " 'index column 8' = index_col(o.name,indid, 8), " & chr(13) &_ " 'index column 9' = index_col(o.name,indid, 9), " & chr(13) &_ " 'index column 10' = index_col(o.name,indid, 10), " & chr(13) &_ " 'index column 11' = index_col(o.name,indid, 11), " & chr(13) &_ " 'index column 12' = index_col(o.name,indid, 12), " & chr(13) &_ " 'index column 13' = index_col(o.name,indid, 13), " & chr(13) &_ " 'index column 14' = index_col(o.name,indid, 14), " & chr(13) &_ " 'index column 15' = index_col(o.name,indid, 15), " & chr(13) &_ " 'index column 16' = index_col(o.name,indid, 16) " & chr(13) &_ "from sysindexes i, sysobjects o " & chr(13) &_ "where i.id = o.id " & chr(13) &_ " and o.name = '"&tmpTABLEName&"' " & chr(13) &_ " and indid > 0 " & chr(13) &_ " and indid < 255 " & chr(13) &_ " and o.type = 'U' " & chr(13) &_ " and (i.status & 64) = 0 " & chr(13) &_ " and (i.status & 8388608) = 0 " & chr(13) &_ " and (i.status & 16777216)= 0" adoRS.open SQL, adoConn, 0 if not adoRS.eof then aryIndex = adoRS.GetRows() else aryIndex = null end if adoRS.close
%> <TABLE width='600' border="1" cellspacing="0" bordercolordark="white" bordercolorlight="#959595"> <TR height="21"> <TD colspan='7' bgcolor='#C9C9C9' align='center'>테이블 정의서</TD> </TR> <TR height="21"> <TD colspan="2" bgcolor='#DFDFDF'> 시스템</TD> <TD colspan='5'> <%=ProjectName%></TD> </TR> <TR height="21"> <TD colspan="2" bgcolor='#DFDFDF' > 작성자</TD> <TD> <%=YourName%></TD> <TD bgcolor='#DFDFDF' > 작성일자</TD> <TD colspan="3"> <%=Date()%></TD> </TR> <TR height="21"> <TD bgcolor='#DFDFDF' colspan='2'> Table ID</TD> <TD> <a name="<%=aryTABLEInfo(2,i)%>"><%=aryTABLEInfo(2,i)%></a></TD> <TD bgcolor='#DFDFDF' > Table Name</TD> <TD colspan='3'> <%=TABLE_des%></TD> </TR> <TR height="21"> <TD colspan='2' bgcolor='#DFDFDF' > Description</TD> <TD colspan='5'> </TD> </TR> <TR height="21"> <TD colspan='2' bgcolor='#DFDFDF' > Primary Key</TD> <TD colspan='5'> <% if isArray(aryPK) then MaxK = Ubound(aryPK,2) Response.Write " " for k = 0 to MaxK if k = MaxK then Response.write aryPK(0,k) else Response.write aryPK(0,k) & ", " end if next end if %> </TD> </TR> <TR height="21"> <TD colspan='2' bgcolor='#DFDFDF'> Foreign Key</TD> <TD colspan='5'> <% Response.Write " " if not isNull(aryRef) then Response.Write "References " & aryRef & "( " MaxFK = Ubound(aryFK,2) for M = 0 to MaxFK if M = MaxFK then Response.Write aryFK(0,M) else Response.Write aryFK(0,M) & ", " end if next Response.Write " )" end if %> </TD> </TR> <% if isArray(aryIndex) then for s = 0 to Ubound(aryIndex,2) %> <TR height="21"> <TD colspan='2' bgcolor='#DFDFDF'> Index info #<%=s+1%></TD> <TD colspan='5'> <% Response.Write " " Response.write aryIndex(1,s) & "(" & aryIndex(2,s) & ")" for u = 3 to 18 if isNull(aryIndex(u,s)) then exit for end if tmpIndexkeys = tmpIndexkeys & aryIndex(u,s) & ", " next Response.Write "<br> Columns(" & Mid(tmpIndexkeys, 1, len(tmpIndexkeys)-2) & ")" tmpIndexkeys = "" %> </TD> </TR> <% next else %> <TR height="21"> <TD colspan='2' bgcolor='#DFDFDF' > Index info #<%=s+1%></TD> <TD colspan='5'> </TD> </TR> <% end if %> <TR bgcolor='#DFDFDF'> <TD width="30" align='center'>No</TD> <TD width="90" align='center'>Physical Name</TD> <TD width="180" align='center'>Logical Name</TD> <TD width="110" align='center'>Data Type</TD> <TD width="40" align='center'>Null</TD> <TD width="70" align='center'>key</TD> <TD width="80" align='center'>Default</TD> </TR> <% for j = 0 to Ubound(aryTABLEInfo,2) if i > Ubound(aryTABLEInfo,2) then exit for end if if tmpTABLEName = aryTABLEInfo(2,i) then tmpTABLEName = aryTABLEInfo(2,i) else exit for end if %> <TR height="21"> <TD align='center' bgcolor='#DFDFDF'> <%=j+1%></TD> <TD> <%=aryTABLEInfo(3,i)%></TD> <TD> <%=aryTABLEInfo(7,i)%></TD> <TD> <% Response.Write aryTABLEInfo(4,i) '정수형 또는 날짜형을 제거하고 싶은 경우 if instr("bit,tinyint,smallint,int,int identity,bigint,smalldatetime,datetime,text,ntext",aryTABLEInfo(4,i)) = 0 then Response.Write "("&aryTABLEInfo(5,i)&")" end if %> </TD> <TD align='center'> <% if aryTABLEInfo(6,i) = "YES" then Response.Write " " else Response.Write "NN" end if %> </TD> <TD> <% Response.write aryTABLEInfo(9,i) if not isnull(aryTABLEInfo(9,i)) and not isnull(aryTABLEInfo(10,i)) then Response.Write ", " Response.write aryTABLEInfo(10,i) %> </TD> <TD align='center'> <% if isNull(aryTABLEInfo(8,i)) then Response.Write " " else tmpLen = len(aryTABLEInfo(8,i)) Response.Write Mid(Mid(aryTABLEInfo(8,i),2),1,tmpLen-2) end if %> </TD> </TR> <% i = i + 1 next i = i - 1 if j < 30 then for l=j+1 to 30 response.write "<tr height='21'><td align='center' bgcolor='#DFDFDF'> "&l&"</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr>" next end if %> </TABLE> <TABLE width='600' border="0"> <TR> <TD height="21" colspan="7"> </TD> </TR> <TR> <TD height="21" colspan="7"> </TD> </TR> <TR> <TD height="21" colspan="7"> </TD> </TR> <TR> <TD height="21" colspan="7"> </TD> </TR> </TABLE> <% next '---------------------------------------------------------------------------------------------------------------------- ' // 디비관련 객체 반환 '---------------------------------------------------------------------------------------------------------------------- SET adoRS = Nothing adoConn.close SET adoConn = nothing
%> | |
| 출처 : 데브피아 |
'프로그래밍 > ASP' 카테고리의 다른 글
| ASP/ 불법 게시물 자동 등록 막기 (0) | 2010.04.23 |
|---|---|
| ASP 템플릿 nTPL-ASP v1.0 (ASP 코드와 HTML 분리) (0) | 2010.04.23 |
| 코딩 규약 (HTML/ASP/JavaScript) (0) | 2010.04.23 |
| 사이트를 지키자!!! (0) | 2010.04.23 |
| 리프레쉬 없이 데이터베이스 내용 가져오기 (0) | 2010.04.23 |