学步园

最近做一个项目, 用的是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);
?>