最近做一个项目, 用的是php 5.3.8, 数据库是sql server 2008,

sql server的管理器,针对每个字段的东西很多,写个注释都要拉滚动条,每写一个得拉一下,非常麻烦.

所以写了一个程序,通过网页列出表和字段,直接可以在网页上写注释,方便多了

 

 

<?php /* Connect to the local server using Windows Authentication and specify the AdventureWorks database as the database in use. */ $serverName = "(local)"; $connectionInfo = array( "Database"=>"zfdb2",'uid'=>'sa','pwd'=>'20100612'); $conn = sqlsrv_connect( $serverName, $connectionInfo); if( $conn === false ) { echo "Could not connect.\n"; die( print_r( sqlsrv_errors(), true)); } if($_POST) { $sql="EXECUTE sp_updateextendedproperty N'MS_Description', N'".$_POST['desc']."', N'SCHEMA', N'dbo', N'TABLE', N'".$_POST['table']."', N'COLUMN', N'".$_POST['col']."'"; sqlsrv_query( $conn, $sql); $sql="EXECUTE sp_addextendedproperty N'MS_Description', N'".$_POST['desc']."', N'SCHEMA', N'dbo', N'TABLE', N'".$_POST['table']."', N'COLUMN', N'".$_POST['col']."'"; sqlsrv_query( $conn, $sql); die( print_r( sqlsrv_errors(), true)); } echo '<iframe name="p" style="display:none"></iframe>'; /* Set up and execute the query. */ $tsql = 'select name from sysobjects where type=\'U\''; $stmt = sqlsrv_query( $conn, $tsql); if( $stmt === false) { echo "Error in query preparation/execution.\n"; die( print_r( sqlsrv_errors(), true)); } /* Retrieve each row as an associative array and display the results.*/ while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)) { if($row['name']=='dtproperties') continue; //echo $row['name']."<hr>"; $sql1="SELECT --TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END, --TableDesc=CONVERT(VARCHAR, ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N'')), 字段id=C.column_id, 字段名称=C.name, 是否主键=CONVERT(VARCHAR,ISNULL(IDX.PrimaryKey,N'')), [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'Y'ELSE N'' END, --Computed=CASE WHEN C.is_computed=1 THEN N'Y'ELSE N'' END, 类型=T.name, 长度=C.max_length, --Precision=C.precision, --Scale=C.scale, 是否可以为空=CASE WHEN C.is_nullable=1 THEN N'Y'ELSE N'' END, 默认值=CONVERT(VARCHAR,ISNULL(D.definition,N'')), 描述=CONVERT(VARCHAR,ISNULL(PFD.[value],N'')) --,IndexName=CONVERT(VARCHAR,ISNULL(IDX.IndexName,N'')), --IndexSort=CONVERT(VARCHAR,ISNULL(IDX.Sort,N'')) -- , Create_Date=O.Create_Date, -- Modify_Date=O.Modify_date FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id LEFT JOIN ( SELECT IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'Y'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id WHERE O.name=N'".$row['name']."' ORDER BY O.name,C.column_id;"; $stmt1 = sqlsrv_query( $conn,$sql1); $i=0; echo '<table>'; while( $c = sqlsrv_fetch_array( $stmt1, SQLSRV_FETCH_ASSOC)) { if($i==0) { echo '<tr bgcolor="#336633" style="color:#ffffff">'; echo '<td colspan="'.count($c).'">  '.$row['name'].'  </td>'; echo '</tr>'; echo '<tr bgcolor="#006699" style="color:#ffffff">'; foreach($c as $n=>$v) { echo '<td>  '.$n.'  </td>'; } echo (isset($_GET['view'])?'':'<td></td>'); echo '</tr>'; } $i++; echo '<tr bgcolor="#'.($i%2==1?'FCFFD2':'D6D6CF').'">'.(isset($_GET['view'])?'':'<form method="post" target="p"><input type="hidden" name="table" value="'.$row['name'].'"><input type="hidden" name="col" value="'.$c['ColumnName'].'">'); foreach($c as $n=>$v) { if($n=='ColumnDesc'&&!isset($_GET['view'])) echo '<td><input type="text" name="desc" value="'.$v.'"></td>'; else echo '<td>'.$v.'</td>'; } if(!isset($_GET['view'])) echo '<td><input type="submit"></td>'; echo (isset($_GET['view'])?'':'</form>').'</tr>'; } echo '</table>'; } /* Free statement and connection resources. */ sqlsrv_free_stmt( $stmt); sqlsrv_close( $conn); ?>